Hi SAS Forum, I have repeated the below "set" statements 35 times, corresponding to Feb 2010 to .......Dec2012. I have already done the job but this code is highly inefficint. Could you please help me to macronize these 35 repeated rounds? data feb_2010_(rename=(balance=FEB2010_balance)) ; set FEB_2010 (keep= bank_number account_number   balance); run; data mar_2010_(rename=(  balance=mar2010_balance)) ; set mar_2010 (keep= bank_number account_number   balance); run; data apr_2010_(rename=( balance=apr2010_balance)) ; set apr_2010 (keep= bank_number account_number   balance); run; data may_2010_(rename=( balance=may2010_balance)) ; set may_2010 (keep= bank_number account_number   balance); run; data jun_2010_(rename=( balance=jun2010_balance)) ; set jun_2010 (keep= bank_number account_number   balance); run; data JUL_2010_(rename=( balance=JUL2010_balance)) ; set JUL_2010 (keep= bank_number account_number   balance); run; data Aug_2010_(rename=( balance=Aug2010_balance)) ; set Aug_2010 (keep= bank_number account_number   balance); run; data Sep_2010_(rename=( balance=Sep2010_balance)) ; set Sep_2010 (keep= bank_number account_number   balance); run; data Oct_2010_(rename=( balance=Oct2010_balance)) ; set Oct_2010 (keep= bank_number account_number   balance); run; data Nov_2010_(rename=( balance=Nov2010_balance)) ; set Nov_2010 (keep= bank_number account_number   balance); run; data Dec_2010_(rename=( balance=Dec2010_balance)) ; set Dec_2010 (keep= bank_number account_number   balance); run; data Jan_2011_(rename=( balance=Jan2011_balance)) ; set Jan_2011 (keep= bank_number account_number   balance); run; data FEB_2011_(rename=( balance=Feb2011_balance)) ; set FEB_2011 (keep= bank_number account_number   balance); run; data Mar_2011_(rename=( balance=Mar2011_balance)) ; set Mar_2011 (keep= bank_number account_number   balance); run; data Apr_2011_(rename=( balance=Apr2011_balance)) ; set Apr_2011 (keep= bank_number account_number   balance); run; data May_2011_(rename=( balance=May2011_balance)) ; set May_2011 (keep= bank_number account_number   balance); run; data Jun_2011_(rename=( balance=Jun2011_balance)) ; set Jun_2011 (keep= bank_number account_number   balance); run; data Jul_2011_(rename=( balance=Jul2011_balance)) ; set Jul_2011 (keep= bank_number account_number   balance); run; data Aug_2011_(rename=( balance=Aug2011_balance)) ; set Aug_2011 (keep= bank_number account_number   balance); run; data Sep_2011_(rename=( balance=Sep2011_balance)) ; set Sep_2011 (keep= bank_number account_number   balance); run; data Oct_2011_(rename=( balance=Oct2011_balance)) ; set Oct_2011 (keep= bank_number account_number   balance); run; data Nov_2011_(rename=( balance=Nov2011_balance)) ; set Nov_2011 (keep= bank_number account_number   balance); run; data Dec_2011_(rename=( balance=Dec2011_balance)) ; set Dec_2011 (keep= bank_number account_number   balance); run; data Jan_2012_(rename=( balance=Jan2012_balance)) ; set Jan_2012 (keep= bank_number account_number   balance); run; data Feb_2012_(rename=( balance=Feb2012_balance)) ; set Feb_2012 (keep= bank_number account_number   balance); run; data Mar_2012_(rename=( balance=Mar2012_balance)) ; set Mar_2012 (keep= bank_number account_number   balance); run; data Apr_2012_(rename=( balance=Apr2012_balance)) ; set Apr_2012 (keep= bank_number account_number   balance); run; data May_2012_(rename=( balance=May2012_balance)) ; set May_2012 (keep= bank_number account_number   balance); run; data Jun_2012_(rename=( balance=Jun2012_balance)) ; set Jun_2012 (keep= bank_number account_number   balance); run; data Jul_2012_(rename=( balance=Jul2012_balance)) ; set Jul_2012 (keep= bank_number account_number   balance); run; data Aug_2012_(rename=( balance=Aug2012_balance)) ; set Aug_2012 (keep= bank_number account_number   balance); run; data Sep_2012_(rename=( balance=Sep2012_balance)) ; set Sep_2012 (keep= bank_number account_number   balance); run; data Oct_2012_(rename=( balance=Oct2012_balance)) ; set Oct_2012 (keep= bank_number account_number   balance); run; data Nov_2012_(rename=( balance=Nov2012_balance)) ; set Nov_2012 (keep= bank_number account_number   balance); run; data Dec_2012_(rename=( balance=Dec2012_balance)) ; set Dec_2012 (keep= bank_number account_number   balance); run; Thanks Mirisage ... View more

Hi SAS Forum, I have 35 proc sort rounds (commencing Feb_2010 and ending Dec_2012). Three out of those 35 are reproduced below. proc sort data=feb_2010_; by bank_number account_number ; run; proc sort data=mar_2010_ ; by bank_number account_number ; run; proc sort data=apr_2010_ ; by bank_number account_number ; run; Writing 35 times is clumsy. So, I tried a parameter macro like below but it doesn't work. %macro name_1 (mth, yr); proc sort data=&mth_&yr_; by bank_number account_number ; run; %mend; %name_1 (mth=feb, yr=2010); %name_1 (mth=mar, yr=2010); %name_1 (mth=apr, yr=2010); Could anyone help me to correct the above macro (or sugggest me a method to cut short writing proc sort steps 35 times. Thanks Mirisage ... View more

Hi SAS Forum, The  variable named “account_number” in dataset A is a numeric (it is sure). I wanted to convert it into a character. I used this code. data B (rename=(account_number_new=account_number));      set A;      account_number_new=left(put(account_number, $32.));  /*to convert to character;*/      drop account_number; run; Question: This warning comes but conversion happened. WARNING: Variable ACCOUNT_NUMBER has already been defined as numeric. Could anyone help me to understand why this warning? Thanks Mirisage ... View more

Hi SAS Forum, I tried to perform this left joining but my code failed. /* have_1 is the “master table” whose records are preserved in the final table*/ /*called “Merged” regardless of “have_2” table is having matching records or otherwise*/ */ data have_1; informat current_date date9.; input Bank_number    Account_number  $ 5-7 Current_date   Arrears_Band $22-28; format current_date date9.; cards; 10  111    30SEP2010 NPNA 10  111    31OCT2010 Current 10  111    30NOV2010 NPNA 10  111    30JUN2011 NPNA 10  111    01JAN2012 NPNA 70  111    31DEC2011 NPNA 70  111    31JAN2012 NPNA 100 111    30APR2011 90 + 100 111    31MAY2011 90 + 100 111    31JUL2011 NPNA 100 111    31AUG2011 NPNA 100 111    04JAN2012 NPNA 40  555    08FEB2010 30 - 60 40  555    31MAR2010 Current 40  555    30APR2010 60 - 90 40  555    31MAY2010 Current 40  666    03JAN2012 1 - 30 ; run; data have_2; informat current_date date9.; input Bank_number    Account_number  $ 5-7 Current_date   Arrears_Band $22-28; format current_date date9.; cards; 10  111    01FEB2012 writoff 70  111    03FEB2012 writoff 70  999    03FEB2012 writoff ; run; /*Using above 2 tables, I needed to create a table like this*/ Bank_number    Account_number Current_date   Arrears_Band 10  111    30SEP2010 NPNA 10  111    31OCT2010 Current 10  111    30NOV2010 NPNA 10  111    30JUN2011 NPNA 10  111    01JAN2012 NPNA 10  111    01FEB2012 writoff /*This record is coming from have_2 table*/ 70  111    31DEC2011 NPNA 70  111    31JAN2012 NPNA 70  111    03FEB2012 writoff /*This record is coming from have_2 table*/ 100 111    30APR2011 90 + 100 111    31MAY2011 90 + 100 111    31JUL2011 NPNA 100 111    31AUG2011 NPNA 100 111    04JAN2012 NPNA 40  555    08FEB2010 30 - 60 40  555    31MAR2010 Current 40  555    30APR2010 60 - 90 40  555    31MAY2010 Current 40  666    03JAN2012 1 - 30 */ /*The is the code I attempted*/ /*Left joining */ proc sql; create table Merged as select * from have_1 as a left join have_2 as b on a.bank_number=b.Bank_number and a.account_number=b.account_number ; quit; Question: 1. My code doesn't do the intended joining (or any joining for that matter) 2). /*And my code creates this warning too*/ /*WARNING: Variable current_date already exists on file WORK.MERGED. WARNING: Variable Bank_number already exists on file WORK.MERGED. WARNING: Variable Account_number already exists on file WORK.MERGED. WARNING: Variable Arrears_Band already exists on file WORK.MERGED.*/ Could anyone help me? Thanks Mirisage ... View more

Hi Forum, Could you please help me to figure out what is the wrong with my macro code below? %macro one (product); proc sort data= a.unified_dataset_&product out= A_&product_sorted;    by bank_number account_number current_date; run; %mend one; %one (product=PL); %one (product=OD); %one (product=MTG); When I ran the above, following error message comes. SYMBOLGEN:  Macro variable PRODUCT resolves to PL WARNING: Apparent symbolic reference PRODUCT_SORTED not resolved. NOTE 137-205: Line generated by the invoked macro "ONE". 21           proc sort data= a.unified_dataset_&product out= A_&product_sorted;    by bank_number account_number current_date; run;                                                                _                                                                22 ERROR 22-322: Syntax error, expecting one of the following: ;, (, ASCII, BUFFNO, DANISH, DATA, DATECOPY, DETAILS, DIAG, DUPOUT,               EBCDIC, EQUALS, FINNISH, FORCE, IN, ISA, L, LEAVE, LIST, MESSAGE, MSG, NATIONAL, NODUP, NODUPKEY, NODUPKEYS,               NODUPLICATE, NODUPLICATES, NODUPREC, NODUPRECS, NODUPS, NOEQUALS, NORWEGIAN, NOTHREADS, OSA, OUT, OVERWRITE,               PAGESIZE, PRESORTED, PSIZE, REVERSE, SIZE, SORTSEQ, SORTSIZE, SORTWKNO, SWEDISH, T, TAGSORT, TECH, TECHNIQUE,               TESTHSI, THREADS, WKNO, WORKNO.  thanks Mirisage ... View more

Hi SAS Forum,  I have a SAS dataset called “have” (attached). I reproduced the same dataset below to lead my querry. Have Obs BANK_NUMBER ACCOUNT_NUMBER Type_DATE TTT_TYPE Wiiii_okk_Total 1 10 111 25-Jun-09 Aaaaa Bnumb nn Bhrrrr-Hff: 66 1,250 2 10 222 6-Jul-10 Aaaaa Bnumb nn Bhrrrr-Hff: 66 -250 3 10 444 3-Jun-11 Aaaaa Bnumb nn Bhrrrr-Hff: 66 0 4 10 444 3-Jun-11 Aaaaa Bnumb nn Bhrrrr-Hff: 66 -50 5 10 444 3-Jun-11 Khgrgi-lff Peveksel: 3 -300 6 10 888 25-Jun-09 Aaaaa Bnumb nn Bhrrrr-Hff: 66 100 7 10 888 12-Feb-10 Khgrgi-lff Peveksel: 3 200 8 10 888 28-Dec-10 Aaaaa Bnumb nn Bhrrrr-Hff: 66 -25 9 10 55555555 3-Jun-11 Aaaaa Bnumb nn Bhrrrr-Hff: 66 125 10 10 55555555 3-Jun-11 Khgrgi-lff Peveksel: 3 -40 Business conditions These are the 2 business conditions that all records of this dataset should obey. if TTT_TYPE  = "Khgrgi-lff Peveksel: 3" then Wiiii_okk_Total should be LE 0 if TTT_TYPE  = "Aaaaa Bnumb nn Bhrrrr-Hff: 66" then Wiiii_okk_Total should be GE 0 Record 7 should have satisfied out first condition above but it violates the condition. So, I need to make Wiiii_okk_Total of record 7 equals to -200 to obey our condition. Records 2, 4, 8 should have satisfied our second condition above but they violate the conditions. So, I need to make Wiiii_okk_Total of records 2, 4, and 8 equals respectably to 250, 50 and 25 to obey our condition. In sum, below is the dataset that I want finally. Want Obs BANK_ NUMBER ACCOUNT_ NUMBER Type_DATE TTT_TYPE Wiiii_okk_Total 1 10 111 25Jun2009 Aaaaa Bnumb nn Bhrrrr-Hff: 66 1250 2 10 222 06Jul2010 Aaaaa Bnumb nn Bhrrrr-Hff: 66 250 3 10 444 03Jun2011 Aaaaa Bnumb nn Bhrrrr-Hff: 66 0 4 10 444 03Jun2011 Aaaaa Bnumb nn Bhrrrr-Hff: 66 50 5 10 444 03Jun2011 Khgrgi-lff Peveksel: 3 -300 6 10 888 25Jun2009 Aaaaa Bnumb nn Bhrrrr-Hff: 66 100 7 10 888 12Feb2010 Khgrgi-lff Peveksel: 3 -200 8 10 888 28Dec2010 Aaaaa Bnumb nn Bhrrrr-Hff: 66 25 9 10 55555555 03Jun2011 Aaaaa Bnumb nn Bhrrrr-Hff: 66 125 10 10 55555555 03Jun2011 Khgrgi-lff Peveksel: 3 -40 My approach data zero;      set a.have;      if TTT_TYPE  = "Khgrgi-lff Peveksel: 3" and Wiiii_okk_Total > 0 then Temp_Wiiii_okk_Total =Wiiii_okk_Total * -1;      else Temp_Wiiii_okk_Total=  Wiiii_okk_Total; run; data first;      set zero ;      if TTT_TYPE  = "Aaaaa Bnumb nn Bhrrrr-Hff: 66" and Wiiii_okk_Total < 0 then Temp_Wiiii_okk_Total =Wiiii_okk_Total * -1;      else Temp_Wiiii_okk_Total = Temp_Wiiii_okk_Total;      drop Wiiii_okk_Total; run; data want (rename =  (Temp_Wiiii_okk_Total = Wiiii_okk_Total ));      set first; run; Question My code generates the “want” table correctly but my code is very inefficient. Could any expert suggest me a way to make it efficient? Thanks Mirisage ... View more