BookmarkSubscribeRSS Feed
Q1983
Lapis Lazuli | Level 10

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

6 REPLIES 6
Haikuo
Onyx | Level 15

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

Jagadishkatam
Amethyst | Level 16

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
Jagadishkatam
Amethyst | Level 16

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
Q1983
Lapis Lazuli | Level 10

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)

Jagadishkatam
Amethyst | Level 16

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
Tom
Super User Tom
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1528 views
  • 3 likes
  • 4 in conversation