Help using Base SAS procedures

Do loop or Macro for repeating variable pull

Reply
Frequent Contributor
Posts: 131

Do loop or Macro for repeating variable pull

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

Respected Advisor
Posts: 3,124

Re: Do loop or Macro for repeating variable pull

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

Trusted Advisor
Posts: 1,128

Re: Do loop or Macro for repeating variable pull

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

Thanks,
Jag
Trusted Advisor
Posts: 1,128

Re: Do loop or Macro for repeating variable pull

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

Thanks,
Jag
Frequent Contributor
Posts: 131

Re: Do loop or Macro for repeating variable pull

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)

Trusted Advisor
Posts: 1,128

Re: Do loop or Macro for repeating variable pull

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

Thanks,
Jag
Super User
Super User
Posts: 6,500

Re: Do loop or Macro for repeating variable pull

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;

Ask a Question
Discussion stats
  • 6 replies
  • 428 views
  • 3 likes
  • 4 in conversation