Hi SAS Forum, Of this data set, I wanted to separate only the subjects having multiple records. data have; informat Current_date date9.; input Bank_number    Account_number  Current_date    Product $ 25-35 Balance     Arrears_Band $ 43-49; Format Current_date date9.; cards; 10 1000000000 31MAR2010    Personal OD 78.72 Current 10 1000000000 31MAY2010    Personal OD 62.84 Current 20 1000000000 31MAY2010    Personal OD 52.84 Current  ; run; Answer: I should get the following data set. Obs       Current_date     Bank_number    Account_number            Product Balance Arrears_Band 10 1000000000 31MAR2010    Personal OD 78.72 Current 10 1000000000 31MAY2010    Personal OD 62.84 Current My effort proc sort data=have  out= have_sorted;    by bank_number account_number current_date; run; data want; set have_sorted; by bank_number account_number current_date; if not first.account_number and last.account_number then output want; run; Problem: I do not get the correct records. Could any one help me. Thanks Mirisage ... View more

Hi SAS Forum, I need to stack the attached two SAS tables so that I will get a table like below.  In below table, the first 5 records are coming from attahced Book2 table. The "bottom-most bolded record" of below table is coming from the attahced Book1 table. It is the corresponding counterpart account in Book1 data set where arrerars_band = writoff (I find difficult to explain this).   Bank_number Current_date Account_number Short_name Currency_code Product_code Currency_short_name Country Transit Balance Product Arrears_Band 10 28-Feb-10 1000000000 OOOOOOOOO RRRRRRRR 0 1 VVV DDDDDDDD Retail 1700 Personal Loan NPNA 10 31-Mar-10 1000000000 OOOOOOOOO RRRRRRRR 0 1 VVV DDDDDDDD Retail 1700 Personal Loan NPNA 10 30-Apr-10 1000000000 OOOOOOOOO RRRRRRRR 0 1 VVV DDDDDDDD Retail 1700 Personal Loan NPNA 10 31-May-10 1000000000 OOOOOOOOO RRRRRRRR 0 1 VVV DDDDDDDD Retail 1700 Personal Loan NPNA 10 30-Jun-10 1000000000 OOOOOOOOO RRRRRRRR 0 1 VVV DDDDDDDD Retail 1700 Personal Loan NPNA 10 6-Jul-10 1000000000 missing missing missing missing missing missing 2216 Personal Loan writoff The account_name called ‘8000000000’in attahced Book2 data set does not have a corresponding counterpart account in Book1 data set where arrerars_band = writoff.   So, I do not want the account_number called 8000000000 to show up in my final stacked output file. AUTiger and Haikuo helped me to do the below code to tackle a "kind of similar situation" but the current situation in my hand is bit different. So, this code is not capable of doing it. proc sort data=A.BOOK1 out=file_A;    by bank_number Account_number ; run; proc sort data=A.BOOK2  Out=file_B;      by bank_number account_number ; run; data merged_file;   set file_B       file_A            ; by bank_number account_number ; run; Would there be way to handle this new situation? Thanks Mirisage ... View more

Hi Colleagues, In this data set, I need to separate the records  where  Arrears_Band at least once has “writoff” value. data have; informat Current_date date9.; Input Current_date   Account_number 11-13 Arrears_Band $ 15-25; 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; /*Answer*/ The data set I want is this. Current_date    Account_number Arrears_Band 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 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 31JAN2012  666  writoff Question: I did below but it obviously selects only the specific records where Arrears_Band =writoff which is not I want. Data want; Set have; If Arrears_Band =’writoff’; Run; Could anybody help me? Thanks Neil ... View more

Hi SAS Forum, I have been trying to merge the attahced two tables for hours using the follwoing code but didn't work. Book1 dataset is having a single record which is not taken into the final mereged file called "COMBINED_file" despite the fact its bank_number and account_number are identically found in book2 talbe as well. Could any help me? Thanks Mirisage proc sort data=A.BOOK1 out=file_A;    by bank_number Account_number ; run; proc sort data=A.BOOK2  Out=file_B;      by bank_number account_number ; run; data COMBINED_file;   merge file_A (IN=a)         file_B (IN=b)            ; by bank_number account_number ; if a and b; run; ... View more

Hi SAS Forum, Apologize if this takes your time. When we post a query, we often get code support to the same problem in diverse ways which is extremely helpful for a SAS learner like me (although I need to do a lot of basic readings to grasp many of them). All these different appraoches provide the same correct resutls. Question: I have been trying to click the green button which implies "correct answer" for all these different coding appraoches but the system allows only one such "click". Is there any method to click "Green correct" button for all correct codes? Thanks Mirisage ... View more

/*Hi SAS Community, I have two tables (Have_1 and Have_2). */ data Have_1; informat Current_date date9.; Input Current_date   Account_number $12-14 Arrears_Band $ 17-23  balance bank_number; Format Current_date date9.; cards; 28FEB2010  111  NPNA    10 100 31MAR2010  111  Current 20 100 31MAY2010  111  30 - 60 30 100 28FEB2010  444  Current 40 900 31MAR2010  444  30 - 60 50 900 30APR2010  444  30 - 60 60 900 31MAY2010  444  Current 70 900 30APR2010  555  Current 80 700 31MAY2010  555  NPNA    90 700 31AUG2011  555  90 +    12 700 30SEP2011  555  NPNA    0  700 31MAY2010  666  Current 15 400 31AUG2011  666  90 +    20 400 30SEP2011  666  Current 30 400 ; run; data Have_2; informat Post_date date9.; Input Post_date Account_number $12-14 Net_Write_off_total_USD bank_number; Format Post_date date9.; cards; 31AUG2010  111  50 100 31AUG2010  111  50 200 30SEP2011  555  77 700 30SEP2011  666  77 300 ; run; Question: I need to join these 2 tables so that I could get the answer below. It is mandatory that I need to keep all the records in have_1 table regardless of whether the have_2 table is having corresponding matching records or not. But reverse is not mandatory, i.e. have_2 table may have excess (non matching) records, and they should not show up in final table that I want.     Answer: /*    Final data set should be like this Notice the 2 records from have_2 table are embedded within have_1 table in date sequence. And a value called “writoff” was assigned. Current_date Account_number   Arrears_Band      balance     bank_number (these are headings of below table) 28FEB2010  111  NPNA    10 100 31MAR2010  111  Current 20 100 31MAY2010  111  30 - 60 30 100 31AUG2010  111  writoff 50 100 28FEB2010  444  Current 40 900      31MAR2010  444  30 - 60 50 900 30APR2010  444  30 - 60 60 900 31MAY2010  444  Current 70 900 30APR2010  555  Current 80 700 31MAY2010  555  NPNA    90 700 31AUG2011  555  90 +    12 700 30SEP2011  555  NPNA    0  700 30SEP2011  555  writoff 77 700 31MAY2010  666  Current 15 400 31AUG2011  666  90 +    20 400 30SEP2011  666  Current 30 400   The code below (Ksharp is acknowledged) generate the wanted table to some extent but I have newly included bank_number variable to reflect business reality. Now the results are misleading us. data want; set have_1 have_2(in=inb  rename=(Post_date=Current_date Net_Write_off_total_USD=balance)) ; by Account_number; if inb then Arrears_Band='writoff '; run; proc sort data=want;by Account_number Current_date;run; This is the output generated by above code. I have striken through 2 records, and they should not be in the output. Reason is their bank_number s are not matching although account_number s are matching. Current_date  Account_number       Arrears_Band  balance         bank_number (these are headings of below table) 28FEB2010 111 NPNA 10 100 31MAR2010 111 Current 20 100 31MAY2010 111 30 - 60 30 100 31AUG2010 111 writoff 50 100 31AUG2010 111 writoff 50 200 28FEB2010 444 Current 40 900 31MAR2010 444 30 - 60 50 900 30APR2010 444 30 - 60 60 900 31MAY2010 444 Current 70 900 30APR2010 555 Current 80 700 31MAY2010 555 NPNA 90 700 31AUG2011 555 90 + 12 700 30SEP2011 555 NPNA 0 700 30SEP2011 555 writoff 77 700 31MAY2010 666 Current 15 400 31AUG2011 666 90 + 20 400 30SEP2011 666 Current 30 400 30SEP2011 666 writoff 77 300 I would appreciate any help to generate the correct table. Thank you Mirisage ... View more