I tested this with only 10 rows. All of them have values, yet it is failing. ... View more

> [pre] > options symbolgen; > %let groupvalues="Lot_Id,Facility"; > %let nrGroupValues = %sysfunc(countw(&groupvalues,' > ')); > %put &nrGroupValues; > > > %macro test; > %if "&groupvalues" ne ' ' %then %do; > nrGroupValues = %sysfunc(countw(&groupvalues,',')); > %do %while(CounterVariable le nrGroupValues); > GroupValue = scan(&groupvalues,CounterVariable,' > ' '); > %put &nrGroupValues; > t CounterVariable; > %put GroupValue; > CounterVariable = CounterVariable + 1; > %end; > %end; > %mend; > %test; > > [/pre] > what is wrong with this code? > it crashes on multiple places > > Message was edited by: Filipvdr I think the problem is that with the Variable CounterVariable, nrGroupValues and GroupValue. The variables are being referenced without '&' and are being initialized without %let. It looks like you are using Datastep Variables and Functions (SCAN) within the macro that does not have a datastep at all. Try this: options nosymbolgen; %let groupvalues="Lot_Id,Facility"; %let nrGroupValues = %sysfunc(countw(&groupvalues,' ')); %put nrGroupValues_1: &nrGroupValues; %let CounterVariable=1; %macro test; %if &groupvalues ne ' ' %then %do; %let nrGroupValues = %sysfunc(countw(&groupvalues,',')); %do %while(&CounterVariable le &nrGroupValues); %let GroupValue = %scan(&groupvalues,&CounterVariable,' '); %put nrGroupValues_2 : &nrGroupValues; %put CounterVariable: &CounterVariable; %put GroupValue: &GroupValue; %let CounterVariable = %eval(&CounterVariable + 1); %end; %end; %mend; %test; Log: nrGroupValues_1: 1 nrGroupValues_2 : 2 CounterVariable: 1 GroupValue: "Lot_Id,Facility" nrGroupValues_2 : 2 CounterVariable: 2 GroupValue: This runs without any error. But I did not understand what you are trying to achieve. Message was edited by: bhavani Message was edited by: bhavani Message was edited by: bhavani ... View more

This solution will definitely work(a little tweaking required). It is indeed simple. Thanks Chang. I do have question though. Is it possible to have a data step do the same. The reason being that I will then have to construct %LET stmts like in the 1st post. Message was edited by: bhavani ... View more

Hi Pete, The Gen Macro is sure a handy utility. But it does not serve my purpose entirely. It only generates a list of sequential macro varaibles(which is very good as i can use is at a number of places). Any work around/suggestions for the issue i stated! Message was edited by: bhavani ... View more

This is the exact Code construct. This is only a psuedo code. Hope it helps. %macro hole_fill_com(year); ** NEEDS AUTOMATION ****; %if &year=2005 %then %do; %let year1=2006; %let year2=2007; %let year3=2008; %let year4=2009;%let year5=2010; %end; %else %if &year=2006 %then %do; %let year1=2005; %let year2=2007; %let year3=2008; %let year4=2009;%let year5=2010; %end; %else %if &year=2007 %then %do; %let year1=2006; %let year2=2008; %let year3=2009; %let year4=2010;%let year5=2005; %end; %else %if &year=2008 %then %do; %let year1=2007; %let year2=2009; %let year3=2010; %let year4=2006;%let year5=2005; %end; %else %if &year=2009 %then %do; %let year1=2008; %let year2=2010; %let year3=2007; %let year4=2006;%let year5=2005; %end; %else %if &year=2010 %then %do; %let year1=2009; %let year2=2008; %let year3=2007; %let year4=2006;%let year5=2005; %end; ****************************************************************************; * YEAR ALTERNATE METHOD *; ****************************************************************************; data X_filled_yr_alt_&year. X_nofill_yr_alt_&year.; length method $10. totals_&year1 totals_&year2 totals_&year3 totals_&year4 totals_&year5 weights_&year1 weights_&year2 weights_&year3 weights_&year4 weights_&year5 8.; if _n_=1 then do; ** NEEDS AUTOMATION ****; declare hash y1(dataset:"A_&year1.",hashexp:10); y1.definekey('proj_key'); y1.definedata("totals_&year1.","weights_&year1."); y1.definedone(); declare hash y2(dataset:"A_&year2.",hashexp:10); y2.definekey('Key'); y2.definedata("totals_&year2.","weights_&year2."); y2.definedone(); declare hash y3(dataset:"A_&year3.",hashexp:10); y3.definekey('Key'); y3.definedata("totals_&year3.","weights_&year3."); y3.definedone(); declare hash y4(dataset:"A_&year4.",hashexp:10); y4.definekey('Key'); y4.definedata("totals_&year4.","weights_&year4."); y4.definedone(); declare hash y5(dataset:"A_&year5.",hashexp:10); y5.definekey('Key'); y5.definedata("totals_&year5.","Weights_&year5."); y5.definedone(); ** ADD HASH FOR NEW DATA AS REQUIRED; call missing(of _all_); end; set X_&year._holes; ** NEEDS AUTOMATION ****; if y1.find()=0 then do; *** DO SOME PROCESSING; output X_filled_yr_alt_&year.; else if y2.find()=0 then do; *** DO SOME PROCESSING; output X_filled_yr_alt_&year.; else if y3.find()=0 then do; *** DO SOME PROCESSING; output X_filled_yr_alt_&year.; else if y4.find()=0 then do; *** DO SOME PROCESSING; output X_filled_yr_alt_&year.; else if y5.find()=0 then do; *** DO SOME PROCESSING; output X_filled_yr_alt_&year.; else output X_nofill_yr_alt_&year.; end; end; end; end; end; run; ****************************************************************************; * GENDER ALTERNATE METHOD *; ****************************************************************************; data X_nofill_yr_alt_&year.; length New_Kwy $6.; set X_nofill_yr_alt_&year.; rename totals_&year.=totals_gend_&year.; if substr(proj_key,3,1)='1' then proj_key_new=substr(proj_key,1,2)||'2'||substr(proj_key,4,3); else if substr(proj_key,3,1)='2' then proj_key_new=substr(proj_key,1,2)||'1'||substr(proj_key,4,3); run; data X_filled_gen_alt_&year. X_nofill_gen_alt_&year.; length method $10. totals_&year totals_&year1 totals_&year2 totals_&year3 totals_&year4 totals_&year5 weights_&year weights_&year1 weights_&year2 weights_&year3 weights_&year4 weights_&year5 8.; if _n_=1 then do; ** NEEDS AUTOMATION ****; declare hash y1(dataset:"A_&year1.",hashexp:10); y1.definekey('proj_key'); y1.definedata("totals_&year1.","weights_&year1."); y1.definedone(); declare hash y2(dataset:"A_&year2.",hashexp:10); y2.definekey('Key'); y2.definedata("totals_&year2.","weights_&year2."); y2.definedone(); declare hash y3(dataset:"A_&year3.",hashexp:10); y3.definekey('Key'); y3.definedata("totals_&year3.","weights_&year3."); y3.definedone(); declare hash y4(dataset:"A_&year4.",hashexp:10); y4.definekey('Key'); y4.definedata("totals_&year4.","weights_&year4."); y4.definedone(); declare hash y5(dataset:"A_&year5.",hashexp:10); y5.definekey('Key'); y5.definedata("totals_&year5.","Weights_&year5."); y5.definedone(); ** ADD HASH FOR NEW DATA AS REQUIRED; call missing(of _all_); end; set X_nofill_yr_alt_&year.; ** NEEDS AUTOMATION ****; if y.find()=0 then do; *** DO SOME PROCESSING; output X_filled_gen_alt_&year.; else if y1.find()=0 then do; *** DO SOME PROCESSING; output X_filled_gen_alt_&year.; else if y2.find()=0 then do; *** DO SOME PROCESSING; output X_filled_gen_alt_&year.; else if y3.find()=0 then do; *** DO SOME PROCESSING; output X_filled_gen_alt_&year.; else if y4.find()=0 then do; *** DO SOME PROCESSING; output X_filled_gen_alt_&year.; else if y5.find()=0 then do; *** DO SOME PROCESSING; output X_filled_gen_alt_&year.; else output X_nofill_gen_alt_&year.; end; end; end; end; end; end; run; ****************************************************************************; * REGION ALTERNATE METHOD *; ****************************************************************************; %macro region_alt(chk_yr,indst,outdst,yr); data X_filled_reg_&yr._alt_&year. &outdst.; length proj_key_1 proj_key_2 proj_key_3 $6. totals_1 totals_2 totals_3 weights_1 weights_2 weights_3 8.; if _n_=1 then do; declare hash re1(dataset:"A_&chk_yr.",hashexp:10); re1.definekey('proj_key_1'); re1.definedata('totals_1','weights_1'); re1.definedone(); declare hash re2(dataset:"A_&chk_yr.",hashexp:10); re2.definekey('proj_key_2'); re2.definedata('totals_2','weights_2'); re2.definedone(); declare hash re3(dataset:"A_&chk_yr.",hashexp:10); re3.definekey('proj_key_3'); re3.definedata('totals_3','weights_3'); re3.definedone(); call missing(of _all_); end; set &indst; *** DO PROCESSING; *** DO PROCESSING; *** DO PROCESSING; if new_weights_&year.=0 then output &outdst.; else output X_filled_reg_&yr._alt_&year.; run; %mend region_alt; ** NEEDS AUTOMATION ****; %region_alt(&year , X_nofill_gen_alt_&year., X_nofill_reg_y_alt_&year. ,y ); %region_alt(&year1, X_nofill_reg_y_alt_&year. , X_nofill_reg_y1_alt_&year.,y1); %region_alt(&year2, X_nofill_reg_y1_alt_&year., X_nofill_reg_y2_alt_&year.,y2); %region_alt(&year3, X_nofill_reg_y2_alt_&year., X_nofill_reg_y3_alt_&year.,y3); %region_alt(&year4, X_nofill_reg_y3_alt_&year., X_nofill_reg_y4_alt_&year.,y4); %region_alt(&year5, X_nofill_reg_y4_alt_&year., X_nofill_reg_y5_alt_&year.,y5); ** NEEDS AUTOMATION ****; proc append base=X_filled_reg_y_alt_&year. data=X_filled_reg_y1_alt_&year. force;run; proc append base=X_filled_reg_y_alt_&year. data=X_filled_reg_y2_alt_&year. force;run; proc append base=X_filled_reg_y_alt_&year. data=X_filled_reg_y3_alt_&year. force;run; proc append base=X_filled_reg_y_alt_&year. data=X_filled_reg_y4_alt_&year. force;run; proc append base=X_filled_reg_y_alt_&year. data=X_filled_reg_y5_alt_&year. force;run; **** DO REMAINING PROCESSING; %mend hole_fill_com; ****************************************************************************; %macro nw_mepscom; %do i = %sysfunc(year(&begsasdt.D)) %to %sysfunc(year(&endsasdt.D)); ** Invoke the HOLE_FILL_COM Macro with the required Paramters ; %hole_fill_com(&i.); %end; %mend; %nw_mepscom; ... View more

Hi Peter, The code i wrote is just a start. If there is better solution that you can suggest, it'll be very helpful. The project is related to Health Care domain. This program actually fills Holes in the data, that is, populating a particular category of Population which is missing in the source survey, using 3 methods, namely, Year Alternate, Gender Alternate and Region Alternate. And eventually a weight is assigned to an individual which will then be projected to National Level Stats. That is, the total number of patients in a particular category multiplied by the Weight is the National Projection This process of filling missing values is done on a Yearly process. That is to say, we do quarterly refreshes and each time, the data increases by a quarter, starting from 01jan2005. Tee weights assignment happens in a very complex manner based on Age Group(0-4(1), 5-25(2), 26-40(3), 41-65(4), 65+(5)), Region of Resindence(E(1),W(2),N(3),S(4)), Gender(Male(1),Female(2)), Drug Claim Indicator, Medical Claim Indicator, Chronic Condition Indicator. We have a reference source A which will have Weights Assigned for all and every possible group of patients.Consider that there is one group (1,1,1,0,0,0) that has a weight of 0. This missing weight has to be populated from one of the following methods(First Non Blank Value) 1. Look for the same group across other years and get the weight (Year Alternate). 2. If NO weight is available in Year Alternate, then check for (1,1,2,0,0,0) and get the New Weight.(Gender Alternate) 3. If NO weight available in Gender Alt, Then the remaing 3 regions are taken into consideration (1,2,1,0,0,0), (1,3,1,0,0,0) and (1,4,1,0,0,0) and a ratio is calculated for each of the three (some formula). The ratio for the region that is close to 1 is considered and the corresponding weight is assigned to our data. The reason why I want to create those macro variables is to eliminate human error(we have about 43 SAS Programs that constitue one build). As the data increases by year, so are the number if macro variables(if done manually, there is a chance that one might not update the code with the required number of Year Macro Variables. My problem does not end there. After the required number of Year macro variables are created, there are the same number of Hash Objects also being created, One hash object per year. These also keep increasing as the number of years of data increases (which is my next peice of automation). Hope the above explanation is helpful. ... View more