hi thanks, when i add an additional & in the %let k=%sysfunc(countw(&&mcr_&i)); %let val = %scan(&&mcr_&i,&j); i get this warning WARNING: Apparent symbolic reference MCR_1 not resolved. ... View more

thanks, quick questions, how can i add an additional case when in the above? ... View more

hi thanks for your comments For the warning i added the list with variables and works. For my question one, my problem is i don't know how to combine "left join" with "case" in one SQL 🙂 ... View more

thanks - i have now edited my initial post - please if you can have a look and advise ... View more

thanks. You are right. i have edited my initial post adding some data - i hope it helps ... View more

thanks 🙂 ... View more

thanks! actually, the months datasets include a column with date values which can be used. i am going to start working on this. I have also added a reply in my initial post with additional information. thanks a lot! 🙂 ... View more

Due to the complexity of what i do and the difficulty to simplify here for all of you to review i think it is an XY problem. i have now added in my initial post a reply with more information - please if you can have a look. thanks a lot for your advises! 🙂 ... View more

thank you! i have replied to my initial comment with additional information ... View more

thanks 🙂 ... View more

hi all, thanks very much  for your answers    i would try to answer to all your questions   I have now managed to make the code run in 3 different separate in order to progress the tasks i have to complete. As you can see the below is far from optimal Background : the scope of the code is to number of different months apply some exclusions (step 1) then create quarterly data (step 2) and finally to be merge in another table (step 3)    I have two problems  1) i want to automate the code. i mean not to need to take the outcome from part one and the to add it in part 2 and then from 2 to 3   2) the below code is an example with 6 months and 2 quarters. I want to make the code general. I mean the user to add a number of months and quarters and not to be static to work only with 6 months and 2 quarters   /* set up*/ %let monhtly_data = month_1 month_2 month_3 month_4 month_5 month_6; %let outcome_names = quarter_1 quarter_2; /*Step-1 Exclusions*/ %macro excl; %let k=%sysfunc(countw(&monthly_data)); %do j=1 %to &k; %let raw_dt = %scan(&monthly_data,&j); data &raw_dt._temp; set &raw_dt (where = (variable_1 = "OP" and variable = "10")); run; %end; %mend; %excl; /*step-2 Convert monthly to quarterly data */ data quarter_1; set month_1_temp month_2_temp month_3_temp; run; data quarter_2; set month_4_temp month_5_temp month_6_temp; run; /* step-3 Create samples based on characteristics */ proc sql; create table quarter_1_smp as select * from quarter_1_char (where = (monthid in (1,2,3))) as x left join quarter_1 as y on x.HOLDING_REFERENCE =y.HOLDING_REFERENCE and x.PERIOD_END_DATE =y.PERIOD_END_DATE; quit; proc sql; create table quarter_2_smp as select * from quarter_2_char (where = (monthid in (4,5,6))) as x left join quarter_2 as y on x.HOLDING_REFERENCE =y.HOLDING_REFERENCE and x.PERIOD_END_DATE =y.PERIOD_END_DATE; quit; ... View more