/*I need to separate the "by group of accts" where  Arrears_Band at least once has “writoff” value. */ data have; informat Current_date date9.; Input Current_date   Account_number Arrears_Band :$15.; Format Current_date date9.; cards; 31MAY2011  111  NPNA 30JUN2011  111  writoff 31JUL2011  111  NPNA 31AUG2011  111  NPNA 30SEP2011  111  NPNA 31OCT2011  111  NPNA 30NOV2011  111  NPNA 31DEC2011  111  NPNA 31JAN2012  111  NPNA 31DEC2011  222  NPNA 31JAN2012  222  NPNA 30NOV2010  333  NPNA 31DEC2010  333  NPNA 31JAN2011  333  NPNA 28FEB2011  333  NPNA 31MAR2011  333  writoff 30APR2011  333  NPNA 31MAY2011  333  NPNA 30JUN2011  333  NPNA 31JUL2011  333  NPNA 31AUG2011  333  NPNA 30SEP2011  333  NPNA 31OCT2011  333  NPNA 30NOV2011  333  NPNA 31DEC2011  333  NPNA 31JAN2012  333  NPNA 28FEB2010  444  Current 31MAR2010  444  30 - 60 30APR2010  444  30 - 60 31MAY2010  444  Current 30JUN2010  444  Current 31JUL2010  444  Current 31AUG2010  444  Current 30SEP2010  444  Current 31OCT2010  444  Current 30NOV2010  444  Current 31DEC2010  444  Current 31JAN2011  444  1 - 30 28FEB2011  444  30 - 60 31MAR2011  444  60 - 90 30APR2011  444  90 + 31MAY2011  444  90 + 30JUN2011  444  90 + 31JUL2011  444  NPNA 31AUG2011  444  NPNA 30SEP2011  444  NPNA 31OCT2011  444  NPNA 30NOV2011  444  NPNA 31DEC2011  444  NPNA 31JAN2012  444  NPNA 28FEB2010  555  30 - 60 31MAR2010  555  30 - 60 30APR2010  555  60 - 90 31MAY2010  555  NPNA 31JAN2012  666  writoff 31JAN2012  777  NPNA ; run; /*Below code does do the job and it separates three "by group of accts", accts 111, 333 and 666*/ data want;   merge have (where=(Arrears_Band='writoff') in=a) have (in=b);    by account_number;    if a and b; run; /*Q: Out of the above three "by group of accts", how could I randomly select only 2 bygroups of accts (my original dataset is having several thousand records)?*/ /*Below proc surveyselect selects only 2 individual rows and does not select 2 by groups*/ PROC SURVEYSELECT DATA=want OUT=need METHOD=SRS   SAMPSIZE=2 SEED=1234567; RUN; /*Thanks Mirisa ... View more

Hi Forum, I have GPA data and socio and demographic data collected from 30 students. My data are like this.   1. GPA - I have continuous values for 30 students such as 3.1, 3.2, 3 and so on (this means I have exact values) 2. Age - I have age categories for the 30 students like (19-22)    (>22 to 25) and so on (I don't have exact age values)   3. Gender - (I have Male,  Female categories for 30 students) 4. Commute time - I have categories for 30 students like (10 mins to 15 mins)  (>15 mins to 30 mins) (I do not have exact commute times) 5. Number of units the student is taking (I don't have exact values but have categories for 30 students) 6. Study time (I don't have exact values but have categories for 30 students) 7. Extra curricular time (I don't have exact values but have categories for 30 students)   Question: I wanted to analyse the impact of above socio demographic factors on GPA. However, I am not too sure what kind of analysis I could do with a continuous dependent variable against categorical independent variables (whether correlation or regression or what?)   Could you please help me to select appropriate analytical method. Thanks   Dunga   ... View more

/*Hi SAS Forum, I have posted this same question in another forum but due to urgency I posted it here too (apologies). I have over million records dataset like this. In this, same cust_num (that is 1) together with same acct_num (that is 111) occur three times and make three by-groups. And there is a single other by group related to acct 222.*/   data have; input cust_num acct_num date score_ID; cards; 1 111 20150324 40000 1 111 20150324 50000 1 111 20150324 60000 1 111 20150324 70000 1 111 20150324 80000 1 111 20150402 40000 1 111 20150402 50000 1 111 20150402 60000 1 111 20150402 70000 1 111 20150402 80000 1 111 20150425 40000 1 111 20150425 50000 1 111 20150425 60000 1 111 20150425 70000 1 111 20150425 80000 2 222 20180302 70000 2 222 20180302 90000 ; run;   /*Q: I need to keep only the latest by group. Answer data set should be this because 20150425 is the latest date out of the three by groups for acct 111. And three is a single other by group for acct 222.   1 111 20150425 40000 1 111 20150425 50000 1 111 20150425 60000 1 111 20150425 70000 1 111 20150425 80000 2 222 20180302 70000 2 222 20180302 90000   Could you please help me to do the coding. We should not use any hard coding like   if date = 20150425;   because I do not know what are the latest dates in other million records   Mirisa   */ ... View more

/*Hi Forum,   I am having over 105 SAS datasets. I have provided below just two datasets to comprehend my question to external readers only. 　 /*EXAMPLE TABLE 1*/ /*Income variable**/ data Have_1; input Income; cards; 0 100 105 100 200 300 300 400 500 1000 ; run;   /*I Define a proc foramt*/ proc format ; value for_income . = 'Missing' low-< 0 = '<0' 0 = '0' 0<- 6 = '>0 - 6' 6 <- 12 = '>6 - 12' 12 <- 60 = '>12 - 60' 60 <- 120 = '>60 - 120' 120 <- 240 = '>120 - 240' 240 <- 360 = '>240 - 360' 360< - high = '>360'; run;   /*I Apply the proc format for "income" variable and generate a sas data table called "want".*/ proc freq data=Have_1 noprint; tables income/list missing out=want; format income for_income.; run;   /*Exporting table "want" to Excel*/ proc export data=want outfile= "\\xxxx\xxx\Final.xls" dbms=excelcs replace label; sheet="Sheet1"; run; 　 /*EXAMPLE TABLE 2*/ /*Distance variable*/ data Have_2; input Distance; cards; 0 . 105102 1000 199 30125 302 400 50000 1000 125 305 1200 ; run;   /*Define a different proc foramt*/ proc format ; value for_distance . = 'Missing' low-< 0 = '<0' 0 = '0' 0 <- 100 = '>0 - 100' 100 <- 1000 = '>100 - 1000' 1000 <- 10000 = '>1000 - 10000' 10000 <- 50000 = '>10000 - 50000' 50000 <- 100000 = '>50000 - 100000' 100000 <- 200000 = '>100000 - 200000' 200000 <- High = '>200000'; run;   /*I Apply the proc format for "distance" variable and generate a sas data table with same name called "want".*/ proc freq data=Have_2 noprint; tables distance/list missing out=want; format distance for_distance.; run; 　 /*Exporting table "want" to Excel*/ proc export data=want outfile= "\\xxxx\xxx\Final.xls" dbms=excelcs replace label; sheet="Sheet1"; run; /*Q: I want to export all the 105 SAS datasets, each called "want" into a single Excel tab so that all 105 tables stack one after the other like below. Could you help me?*/ 　 　 Income COUNT PERCENT 0                       1     10 >60 - 120   3      30 >120 - 240 1     10 >240 - 360 2      20 >360                3      30 　 　 Distance                      COUNT PERCENT .                                      1             7 0                                      1             7 >100 - 1000             7             53 >1000 - 10000        1             7 >10000 - 50000      2            15 >100000 - 200000 1             7 ... View more

/*Hi SAS Forum,   I have two datasets. One set provides the income of k number of houses in Jun. Other set provides the income of p number of houses in May. My question is further down. */ 　 /*Jun**/ data Have_Jun; input Income; cards; 0 100 105 100 200 300 300 400 500 1000 ; run;     /*May*/ data Have_May; input Income; cards; 0 . 105 100 199 301 302 400 500 1000 125 305 1200 ; run; 　 /*Q: I want to generate a proc tabulate table like below (in fact, I have to produce 75 proc tabulate tables for huge datasets).     Income Missing 0 >0-100 >100-200 >200-300 >300-400 >400-500 >500-1000 >1000 sum Jun COUNT   1 2 2 2 1 1 1   10   PERCENT 0.0% 10.0% 20.0% 20.0% 20.0% 10.0% 10.0% 10.0%     May COUNT 1 1 1 3   4 1 1 1 13   PERCENT 7.69% 7.69% 7.69% 23.08% 0.00% 30.77% 7.69% 7.69% 7.69%   Diff   -7.69% 2.31% 12.31% -3.08% 20.00% -20.77% 2.31% 2.31% -7.69%     This table simply presents counts of subjects in each income category for June and May separately. And it presnets total sums separately. And it presents corresponding perecentages separately. And it presents difference in percentages between Jun and May in each income category.   /*I have done up to this but I cannot handle the proc trabulate part. Would appreciate any help?*/ /*created a format*/ proc format ; value val .= 'Missing' low-<0 = '<0' 0 = '0' 0<-100 = '>0-100' 100<-200 = '>100-200' 200<-300 = '>200-300' 300<-400 = '>300-400' 400<-500 = '>400-500' 500<-1000 = '>500-1000' 1000<-high = '>1000'; run;   /*applied the format for Jun*/ data need_jun; set have_jun; income_category = put (income, val.); Flag='Jun'; run;   /*applied the format for May*/ data need_may; set have_may; income_category = put (income, val.); Flag = 'May'; run;   /*concatenate 2 tables together*/ data want; set need_may need_jun; run;   /*Apply a proc tabulate*/ PROC TABULATE DATA=want; VAR income; class income_category flag; TABLE flag, income_category, income*N ; RUN; ... View more

/*Hi SAS Forum, I have posted this same question in another forum and am repeating here too due to urgency of resutls generation. I am having below dataset. For variable "income", value 999 means the income of the person is actually $999. However, for "Age" varaible, the value 999 means the value is unknown or missing. */ Data have; input income Age; cards; 100 999 200 25 999 30 ; run; /*Q: I want to calcualte min, max and mean of these 2 vairables using proc means. We cannot use other methods but must use proc means for calculating min, max and mean (otherwise it upsets our original long SAS code). Answer: for income, min=100  max=999  mean=433 for Age min=25  max=30  mean=27.5  (because value 999 should be omitted from Age variable) I attmepted below code, but getting me no where. Could an expert help me. Thanks, Mirisa*/ %macro any (field); data want; set have; %if &field ="income" %then %do; proc means data=have;   var &field; run; %end; %mend any; %any (income); %any (Age); ... View more

Hi SAS Forum,   /*I am having this dataset with a single variable called income and the dataset has no Ids.   I need to categorize the income variable into bands.   I achieved it using the proc format. 　 Then Proc freq below generates a dataset called want_1.   Q: I wanted to get the want_1 dataset rotated so that the final answer dataset should be like below (but without using proc tabulate the finally rotated answer dataset should be generated). Could anyone help me?   Thanks This should be the answer dataset.   Income        0     >0-100      >100-200      >200-300     >300-400     >400-500      >500-1000 COUNT        1     2               2                   2                  1                      1                   1 PERCENT   10     20            20               20                   10                  10 1                0   Same answer table is repeated below. Income 0 >0-100 >100-200 >200-300 >300-400 >400-500 >500-1000 COUNT 1 2 2 2 1 1 1 PERCENT 10 20 20 20 10 10 10 　 　 */ 　 　 data have; input Income; cards; 0 100 105 100 200 300 300 400 500 1000 ; run; proc format ; value val .= 'Missing' low-<0 = '<0' 0 = '0' 0<-100 = '>0-100' 100<-200 = '>100-200' 200<-300 = '>200-300' 300<-400 = '>300-400' 400<-500 = '>400-500' 500<-1000 = '>500-1000' 1000<-high = '>1000'; run;   proc freq data=have noprint; tables Income/list missing out=want_1 sparse; /*SPARSE option includes zero cell frequencies in the output data set;*/ format Income val.; run;   ... View more

/* Hi Forum,   I have a dataset like below. It provides the income of 2 households that occur in different time periods. Q: I wanted to get the min, max and mean household income of the entire sample. I have used the Approach I and II below which generate very different 2 answeres. Could you please tell me which approach is correct to get the mean income of households in the sample? */ data data1; input HOUSE_ID Date Income; cards; 111 20170101 25 111 20170208 30 111 20170617 . 333 20170623 400 333 20170705 -0.001 333 20170718 4000 ; run; /* Approach I:*/ Proc means data = data1; Var income; Run; /*Answer: Min =-0.001 mAX=4000 Mean = 890*/ /*Approach II*/ 　 　 proc means data=data1 noprint nway; /*nway keyword is necessary*/ class House_ID; var Income; output out=data2 mean=Income_mean; run; 　 proc means data=data2; var Income_mean; run; /*Answer: Min= 27.5 Max = 1466.67 Mean =747*/ /*Thansk*/   ... View more

/*Hi SAS Forum,   I have a dataset like below. I want to categorize the outsatnding_balance into bands. 　 */ data have; input OS_Balance; cards; . -999.1 -999 -998.7 -500 -100 -75 -50 -25 0 0.00001 ; run; /* I have created the below proc format and it does seem correctly doiing the banding. Q: However, my gut suggets me that the way I have defined the banding is unconventional/awkward. Could an expert propose an alternative standard way of defining negative bands? */ 　 proc format fmtlib; value val . = 'Missing' low - < -999 = '<-999' -999 = '-999' -999 <- -500 = '>-999 - -500' -500 <- -100 = '>-500 - -100' -100 <- -75 = '>-100 - -75' -75 <- -50 = '>-75 - -50' -50 <- -25 = '>-50 - -25' -25 <- <0 = '>-25 - <0' 0 = '0' 0 <- High = '>0' ; run; data want_1; set have; variable= put (OS_Balance, val.); run; 　 proc freq data=want_2; tables LENDING_UTIL_PERCENTAGE/list missing; format LENDING_UTIL_PERCENTAGE val.; run;   /*Thanks Mirisa*/ ... View more