Hi Collegues, I have the attahced data set with a single variable. Problem: I need to categorize these obs into some meaningful income groups not driven by any business logic but based on the distribution of data. I do not have any clue what cut off points that I should impose for getting the groups. I was just given a huge data set by the employer. So, I have run the below code to get a sense how to decide the "income group boundaries" proc univariate data = have plots; var income; run; Then I got the following "Quantiles". 100% Max 393 99% 393 95% 392 90% 360 75% Q3 138 50% Median 41 25% Q1 0 10% 0 5% -9 1% -10 0% Min -10 Based on above qunatiles, I decided the following boundaries. data want; length Income_Range $20; set have; if Income = . then Income_Range='Missing'; else if Income   <=-9   then Income_Range = '<=-9'; else if Income   <=-8   then Income_Range = '-8 to -9'; else if Income   <=-1   then Income_Range = '-1 to -8'; else if Income   <=0  then Income_Range ='-7 to 0'; else if Income   <=41 then  Income_Range ='1-41'; else if Income   <=392 then  Income_Range ='42-392'; else Income_Range = '>392'; run; Question: 1). Can we use Univariate approach like this to decide "boundaries" for income range if we do not have any clue what the boundary cut offs are? 2). My SAS code looks fine for this small data set, but they sometimes dangerously omit some data when apply to large data set. Could any expert make sure this code is error free Thank you for the help Mirisage ... View more

Hi Collegues; I have this data set - note it has 4 accounts. data a; informat Current_date date9.; Input Current_date   Account_number $11-13 Arrears_Band $ 15-21; Format Current_date date9.; cards; 28FEB2010  111  NPNA 31MAR2010  111  Current 30APR2010  111  30 - 60 28FEB2010  444  Current 31MAR2010  444  30 - 60 30APR2010  444  30 - 60 31MAY2010  444  Current 31OCT2010  444  Current 31DEC2010  444  Current 31JAN2011  444  1 - 30 28FEB2011  444  30 - 60 31MAR2011  444  60 - 90 30JUN2011  444  90 + 31JUL2011  444  NPNA 31AUG2011  444  NPNA 28FEB2010  555  30 - 60 31MAR2010  555  30 - 60 30APR2010  555  60 - 90 31MAY2010  555  NPNA 31AUG2011  555  90 + 30SEP2011  555  NPNA 31MAY2010  666  Current 31AUG2011  666  90 + 30SEP2011  666  Current ; run; Question: I want to remove any Account_number that has fallen at least once from a "non 90 +” Arrears_Band to NPNA Answer Based on this condition, Account_number 555 has to be removed. My appraoch 1. I have used the below code helped by PG Stat to track how each account has successively fallen across different Arrears_Bands. 2. Then visually spotted the account_numbers that satisfy the above condition.  3. Finally, I did a "sub setting if" step, using those spotted account_numbers. But I have a huge data set which takes time to do this. Could any one suggest an efficient alternative for the last step? I used this approach.  proc sort data=a; by account_number Current_date; run; data cat(keep=account_number category); length Category $200; do until(last.account_number); do until (last.Arrears_Band);   set a;   by account_number Arrears_Band notsorted; end; category = catx(" to ", category, Arrears_Band); end; run; data b; set a; if account_number not in ('555'); run; /*Thanks Mirisage ... View more

Hi Colleagues, Attached is a small part of my huge dataset. I need to identify the “worst ever” delinquency bucket each customer has ever fallen into. Delinquency is defined as any value in the data set except “CURRENT”. The worst delinquency band is “DWO” (not found in this sample data set but I have in my huge data set, so need to code this too) Next worst is “NPNA” Next worst is “90+days” (not found in this sample data set but I have in my huge data set, so need to code this too) Next worst is “31-90days” Least worse is “1-30days” Answer: Account            worse ever delinquency bucket the customer has ever fallen 44                          NPNA               99                       1-30days My Effort: I reviewed the literature and found that this is a problem related to “working across variables” as opposed to “working across observations”. I was manage only to develop a character array like below. DATA want;    SET have;    ARRAY Amth_1(13)  $ mth_1  - mth_13; run; Could any one of you help me. Thanks Mirisage ... View more