About JinboZhao

JinboZhao · ‎07-17-2017

Hi SAS, I have a dataset contains some character variables and other categorical variables but displaying with numbers. I would like to generate a table the following: MARITAL_STATUS MARITAL_STATUS Frequency Percent Cumulative Frequency Cumulative Percent C 10 0.1 10 0.1 D 20 0.2 30 0.3 M 30 0.3 60 0.6 O 10 0.1 70 0.7 S 10 0.1 80 0.8 W 20 0.2 100 1 I did this using PROC FREQ DATA= ; TABLES _CHARACTER_; RUN; However, this is only works for thsoe variables which contain chararcter values. For those variables have numeric values, how could I get the similar result? I gave a sample table here as well. Grade Indicator Frequency Percent Cumulative Frequency Cumulative Percent 1 10 0.1 10 0.1 2 20 0.2 30 0.3 3 30 0.3 60 0.6 4 10 0.1 70 0.7 5 10 0.1 80 0.8 6 20 0.2 100 1 Hope anybody can help me. Many appreciation.

JinboZhao · ‎07-06-2017

Hi SAS, I have a dataset contains PERIOD_DT, START_DT, END_DT and other variables and millions of observations, like below: ID START_DT END_DT GRADE QUALITY PERIOD 1 31 January 2008 28 February 2008 A E 31 May 2008 1 29 February 2008 30 March 2008 V C 31 May 2008 1 31 March 2008 30 October 2008 D D 31 May 2008 1 31 October 2008 27 November 2008 D D 31 May 2008 1 28 November 2008 29 November 2009 E E 31 May 2008 1 30 November 2009 29 April 2012 S T 31 May 2008 2 31 January 2008 30 March 2008 B B 31 May 2008 2 31 March 2008 30 July 2008 A R 31 May 2008 2 31 July 2008 28 August 2008 V V 31 May 2008 2 29 August 2008 30 October 2008 D D 31 May 2008 2 31 October 2008 30 December 2008 D Y 31 May 2008 2 31 December 2008 29 January 2009 E E 31 May 2008 2 30 January 2009 29 April 2009 S A 31 May 2008 2 30 April 2009 29 September 2009 B S 31 May 2008 One ID has many none overlap START and END records. I want to get a new table which only contains the observations which the PERIOD time is within the START and END period. According to above sample, I want to get a table like: ID START_DT END_DT GRADE QUALITY PERIOD 1 31 March 2008 30 October 2008 D D 31 May 2008 2 31 March 2008 30 July 2008 A R 31 May 2008 Many appreciation for your help.

JinboZhao · ‎07-06-2017

Thank you very much. I tried to get the merge table first and then want to do the condition filter which you used DATEPART(t2.VALID_START_DT) <= t1.PERIOD_DT <= DATEPART(t2.VALID_END_DT). Could you tell me, which SAS process can do this selection? Can you show me the code?

JinboZhao · ‎07-06-2017

Thank you. I followed your code, it run successfully on my SAS, but some variables' values are missing. There are no values at all in some variables, like ACCOUNT_SK, CUSTOMER_SK. This is my code: DATA NEWFORMAT101; SET TL.BRIGE_101; VALID_START_DT=DATEPART(VALID_START_DTTM); VALID_END_DT=DATEPART(VALID_END_DTTM); FORMAT VALID_START_DT VALID_END_DT DATE9.; RUN; PROC SQL; CREATE TABLE NEW101 AS SELECT DISTINCT t1.TARGET, t1.ACCOUNT, t2.ACCOUNT_SK, t2.CUSTOMER_SK, t1.PERIOD_DT, t1.TIME_SK, t2.VALID_START_DT, t2.VALID_END_DT, t2.PROCESSED_DTTM FROM TL.TARGET_TIME101 t1 LEFT JOIN NEWFORMAT101 t2 ON (t1.ACCOUNT = t2.ACCOUNT) AND DATEPART(t2.VALID_START_DT) <= t1.PERIOD_DT <= DATEPART(t2.VALID_END_DT); QUIT; Do you know why? Thank you.

JinboZhao · ‎07-06-2017

Thank you, I got the result from step1. For step 2, I want to replace the date values to numeric values. Because I want to join two dataset, dataset 1 has ID, VALID_START_DTTM, VALID_END_DTTM and other variables . Dataset 1 has millions of records, every ID has many no overlap START and END date. DATASET 1 ID VALID_START_DTTM VALID_END_DTTM QUALITY GRADE 1 31JAN2008:00:00:00.000000 28FEB2008:23:59:59.000000 A A 1 29FEB2008:00:00:00.000000 30MAR2008:23:59:59.000000 B E 1 31MAR2008:00:00:00.000000 30OCT2015:23:59:59.000000 C D 1 31OCT2015:00:00:00.000000 27NOV2016:23:59:59.000000 D E 1 28NOV2016:00:00:00.000000 29NOV2017:23:59:59.000000 E A 2 30NOV2009:00:00:00.000000 29APR2012:23:59:59.000000 A E 2 30APR2012:00:00:00.000000 29APR2013:23:59:59.000000 B D 2 30APR2013:00:00:00.000000 30MAY2013:23:59:59.000000 C E 2 31MAY2013:00:00:00.000000 30OCT2015:23:59:59.000000 D A 2 31OCT2015:00:00:00.000000 30JAN2016:23:59:59.000000 E E 2 31JAN2016:00:00:00.000000 28AUG2017:23:59:59.000000 A A Dataset 2 likes the following, but the TIME value is specific to 101. ID TIME 1 101 2 101 3 101 Time 101 is corresponding to time 31MAY2016 in my working enviroment. I want to get a joined table, which includes ID, TIME (only has value101) and QUALITY and GRADE. The condition is, TIME 101, the same as 31MAY2016 must be in one of each ID's START and END period. The final result should like this: ID VALID_START_DTTM VALID_END_DTTM QUALITY GRADE TIME 1 31OCT2015:00:00:00.000000 27NOV2016:23:59:59.000000 D E 101 2 31JAN2016:00:00:00.000000 28AUG2017:23:59:59.000000 A A 101 Hope this time, I say it clearly. Thank you.

JinboZhao · ‎07-06-2017

Hi SAS, I have two datasets, and I want to do the following steps in SAS, but didnot get the result. 1. For the table 1, there are two date variables, have the FORMAT: DATETIME26.6 and INFORMAT: DATATIME26.6. In the table, their value likes this: 29FEB2008:00:00:00.000000. I want those date values show like 29FEB2008. This is part of my dataset: ID VALID_START_DTTM VALID_END_DTTM 1 31JAN2008:00:00:00.000000 28FEB2008:23:59:59.000000 2 29FEB2008:00:00:00.000000 30MAR2008:23:59:59.000000 3 31MAR2008:00:00:00.000000 30OCT2008:23:59:59.000000 4 31OCT2008:00:00:00.000000 27NOV2008:23:59:59.000000 5 28NOV2008:00:00:00.000000 29NOV2009:23:59:59.000000 6 30NOV2009:00:00:00.000000 29APR2012:23:59:59.000000 7 30APR2012:00:00:00.000000 29APR2013:23:59:59.000000 8 30APR2013:00:00:00.000000 30MAY2013:23:59:59.000000 9 31MAY2013:00:00:00.000000 30OCT2013:23:59:59.000000 10 31OCT2013:00:00:00.000000 30JAN2014:23:59:59.000000 11 31JAN2014:00:00:00.000000 28AUG2014:23:59:59.000000 My coding is: DATA NEWFORMAT101; SET TL.BRIGE_101; FORMAT VALID_START_DTTM VALID_END_DTTM DDMMYY8.; RUN; But, I only got ******** for values in those two variabels. Could you help me? 2, Once I get the result from step 1, I want to change those values to numeric values in both VALID_START_DTTM and VALID_END_DTTM variables, according to the following table: PERIOD_ID TIME_ID 29 February 2016 98 31 March 2016 99 30 April 2016 100 31 May 2016 101 30 June 2016 102 31 August 2016 104 3, after get the result from step2, I want to select observations which the time 101 is included within the VALID_START_DTTM and the VALID_END_DTTM period. Thank you.

JinboZhao · ‎06-29-2017

Hi, my dataset is like this: ID OPENTIME CLOSETIME GENDER GRADE LOANS FLAG 1 98 121 F A 1200 Y 2 95 115 M B 1300 Y 3 96 114 M C 1500 N 4 99 120 F D 1600 Y 5 98 107 F E 1700 N The following is the code I use: proc means data=table n mean min max std q1 q3 qrange median nmiss ; var _numeric_; run; proc freq data=table; tables _character_; run; But the results for numeric variables do not include the percentages. Could you help me, as I want to get all those results in one report.

JinboZhao · ‎06-29-2017

Thank you. I got most of them, but could not get the percentage ones. Could you help me?

JinboZhao · ‎06-29-2017

Hi Guys, I hope to get a data quality report which includes the following indicators: var mean std min max N Q1 median Q3 IQ_Range n_low n_low_percent n_high n_high_percent n_far_low n_far_low_percent n_far_high n_far_high_percent null_rate missing missing_percent Could anyone show me the respective code in SAS proc print, or proc freq, or other useful SAS procedure? Many appreciation.

JinboZhao · ‎06-28-2017

Hi, I have a problem of output observations which contains missing values in some variables, but not all the variables. Currentlly, I have a dataset, which contains 53 character variables and 24 numeric variables, and about 100000 observations. I would like to output a new table which contains those observations, which have missing values in some variables, but not all the variables. Or, I like to output each table for each variable, which only contains observations which contain missing values under this variable. Could anyone show me code to do this? Many appreciation.

JinboZhao · ‎06-28-2017

Thank you. I just read one paper, it showed that SAS macro can solved this problem. However, it is not working good in my pc. (This is the link. https://www.mwsug.org/proceedings/2011/pharma/MWSUG-2011-PH03.pdf) I also copied the beginning part of the codeing in the following. Is there any possible, you could explain me in detail how to inuput the xlsfile, indata, outfile path to my macro? I tried to create new excels in my D drive, and did like this : %macro onedata (xlsfile=D:\Scorecard Jinbo\input, indata=training_dataset, outfile=D:\Scorecard Jinbo\, trim=0.1); but not success. Could you help me about this? The coding: /******User guide****** xlsfile: Input the path and excel file name which the missing and outlier is output to. indata: Input the data name which you want to check. outfile: Input the path and file name for rtf report. trim: Input the percentage you want to trim when calculate mean and SD. ***********************************************************/; /********** Check for one dataset**********/ ods listing close; %macro onedata (xlsfile=, indata=, outfile=, trim=); /*Create content table*/ proc contents data=&indata out=tmp(keep=memname name type label); run; /*Check frequency for Character variable, mean for numerical variable*/ /*Get the number of character and numerical variable*/ proc freq data=tmp; table type; ods output Freq.Table1.OneWayFreqs=tmp1(keep=type frequency); run;

JinboZhao · ‎06-28-2017

Hi masters, Now I have a dataset, including about 50 character variables and about another 50 numeric variables and millions of observations. I would like to have a SAS report, which will include the counts and percentages of each category under each character variable (the count, the category, the percentage and also the missing values), and the statistical property for each numerical variables (like count, mean, standard deviation, and etc). Could anyone tell me, what SAS procedures I should use to get this kind of report? Or, is there any SAS code can count those character and numeric variables separately? Moreover, could anyone tell me, how could I export missing values for each variable to a new dataset? Many appreciation.

JinboZhao · ‎06-21-2017

Thanks for your help. I have a further question. How could I draw line graphs for the frequency variables for each category? I tried to emport the data to excel, but could not do it. Thank you.

JinboZhao · ‎06-21-2017

Thank you very much for your solution. I already got the result. By the way, when I tried to look at the percent, it only desplays two decimals. Like : Percent 0.01 Could you tell me how could I modift the decinmal, to get the result like Percent 0.0985? Thank you

JinboZhao · ‎06-21-2017

Hi, I have two columns, one is time variable, another one is the character variable. My data looks like below. I would like to get the frequency resuts like the below one. Could anyone show me the code to do this? My original data: Time Character 1 A 2 B 3 B 4 C 5 D 1 A 2 C 3 B 4 D 5 A 1 B 2 C 3 D 1 C 2 D 3 B 4 A 5 A I would like to get the result like: Count of Character Column Labels Row Labels A B C D Grand Total 1 2 1 1 4 2 1 2 1 4 3 3 1 4 4 1 1 1 3 5 2 1 3 Grand Total 5 5 4 4 18 I tried use proc freq, but only got the seperate statistical results for each variable. Could anyone show me how to get the result like the second table? Many appreciation.

Online Status	Offline
Date Last Visited	‎08-23-2017 06:02 AM

Re: SAS Miner WOE change interval groups to discrete integer groups

Re: SAS Miner WOE change interval groups to discrete integer groups

SAS Miner WOE change interval groups to discrete integer groups

Different regression result on same dataset from both Enterprise Miner...

Re: Out-of-time Logistic regression test

Re: Out-of-time Logistic regression test

Out-of-time Logistic regression test

How to format negative numbers?

SAS Miner INTERACTIVE GROUPING stop run

Re: DATA REPLACE VALUES WITH SCORES

Count numeric value's frequency

time data selection

Re: SAS TIME FORMAT AND SELECT WITHIN TIME PERIOD

Re: SAS TIME FORMAT AND SELECT WITHIN TIME PERIOD

Re: SAS TIME FORMAT AND SELECT WITHIN TIME PERIOD

SAS TIME FORMAT AND SELECT WITHIN TIME PERIOD

Re: Data quality indicators report

Re: Data quality indicators report

Data quality indicators report

SAS OUTPUT OBSERVATIONS WITH MISSING VALUES

Re: SAS DATA QUALITY TESTING

SAS DATA QUALITY TESTING

Re: sas character and numeric variables frequency count and analysis (...

Re: sas character and numeric variables frequency count and analysis (...

sas character and numeric variables frequency count and analysis (like...