topic Re: Do loop or Macro for repeating variable pull in SAS Procedures

Do loop or Macro for repeating variable pull

Q1983 — Fri, 29 Aug 2014 23:22:48 GMT

proc sql;

create table step as

select ln,step_cd,compl_dt

from table1

where ste_cd in ('333','M22','444');

quit;

Sample Results

ln step_cd compl_dt

11 333 8/11/2013

11 M22 12/15/2012

12 444 9/15/2013

data s333;

set table1;

by ln;

if step_cd ='333';

s333_dt=compl_dt;

run;

data M22;

set table1;

by ln;

if step_cd ='M22';

M22_dt=compl_dt;

run;

data s444;

set table1;

by ln;

if step_cd ='444';

s444_dt=compl_dt;

run;

This creates separate dataset for each step cd. A ln can appear in multiple datasets based on the ln

Is there a way to automate the pull using a do loop or an array? Note that if a step_cd begins with a number I place a character "s" in front. I just listed 3 step_cd in this example. In reality I have about 20 step cd. I am trying to avoid repeating the routine shown in the example

Re: Do loop or Macro for repeating variable pull

Haikuo — Sat, 30 Aug 2014 02:51:26 GMT

data have;

input ln step_cd$ compl_dt :mmddyy10.;

cards;

11 333 8/11/2013

11 M22 12/15/2012

12 444 9/15/2013

;

data _null_;

set have;

if nvalid(step_cd,'v7')=1 then

call execute ('data ' || step_cd || '; set have; if step_cd = "'|| step_cd || '";' || step_cd || '=compl_dt;run;');

else

call execute ('data ' ||'s'|| step_cd || '; set have; if step_cd = "'|| step_cd || '";' || 's'||step_cd || '=compl_dt;run;');

run;

Haikuo

Re: Do loop or Macro for repeating variable pull

Jagadishkatam — Sat, 30 Aug 2014 06:52:16 GMT

Indeed suggestion is very apt for the purpose. The code below is inspired from the same with few changes

data table1;

input ln step_cd$ compl_dt: mmddyy10.;

if compress(step_cd,,'ka') ne '' then step_cd=step_cd;

else step_cd=cats('s',step_cd);

format compl_dt date9.;

cards;

11 333 8/11/2013

11 M22 12/15/2012

12 444 9/15/2013

;

data _null_;

set table1;

call execute('data ' ||step_cd||

';set table1;by ln;if step_cd ="' ||trim(step_cd)||

'";' ||trim(step_cd)|| '_dt=' ||compl_dt||

';format ' ||trim(step_cd)|| '_dt date9.;run;');

run;

Thanks,

Jag

Re: Do loop or Macro for repeating variable pull

Jagadishkatam — Sat, 30 Aug 2014 08:13:12 GMT

Alternatively as you wanted by do loop and macros.

proc sql;

select distinct step_cd into: step separated by ' ' from table1;

quit;

%put &step;

%macro ex();

data _null_;

%do i = 1 %to 3;

%let var= %scan(&step.,&i,' ');

data &var.;

set table1;

by ln;

if step_cd ="&var.";

&var._dt=compl_dt;

format &var._dt date9.;

run;

%end;

%mend;

%ex;

Thanks,

Jag

Re: Do loop or Macro for repeating variable pull

Q1983 — Sat, 30 Aug 2014 17:12:44 GMT

The do loop alternative works as long as the step begins with a character (ie M22). If it begins with a numeric I get an error because sas does not allow you to use leading numerics for variable names. How would the code be modified to place say an ls in from of the step. (ie instead of 333 show ls333)

Re: Do loop or Macro for repeating variable pull

Jagadishkatam — Sat, 30 Aug 2014 17:23:33 GMT

i agree, dataset name starting with numeric values cannot be created. to avoid the same there is a way, either you can create a variable with probable dataset names by concatenating the character values to the numeric values at the creation of the dataset. like below . then you can use the same dataset to create the macro variable with all dataset names starting with character names like i suggested in my earlier code

data table1;

input ln step_cd$ compl_dt: mmddyy10.;

if compress(step_cd,,'ka') ne '' then step_cd=step_cd;

else step_cd=cats('ls',step_cd);

format compl_dt date9.;

cards;

11 333 8/11/2013

11 M22 12/15/2012

12 444 9/15/2013

;

Hope this helps.

Thanks,

Jag

Re: Do loop or Macro for repeating variable pull

Tom — Sun, 31 Aug 2014 15:49:03 GMT

If the list of possible values of STEP_CD is small enough then you can just generate the code into macro variables.

data table1 ;

input ln step_cd $ compl_dt ;

informat compl_dt mmddyy10.;

format compl_dt yymmdd10. ;

cards;

11 333 8/11/2013

11 M22 12/15/2012

12 444 9/15/2013

;;;;

proc sql noprint ;

select distinct

case when nvalid(step_cd) then step_cd else 'S'||step_cd end as memname

, catx(' ','when',cats('(',quote(trim(step_cd)),')'),'output',calculated memname)

into

:dslist separated by ' '

, :whenlist separated by ';'

from table1

;

quit;

data &dslist ;

set table1 ;

select (step_cd);

&whenlist;

otherwise ;

end;

run;