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
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
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
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
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)
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.