DATA Step, Macro, Functions and more

using column values to become variable names

Reply
Regular Learner
Posts: 1

using column values to become variable names

Hi, have the following data set which captures key process dates by an ID key....

 

ID_keyprocessprocess_date
1x01-01-15
1y02-01-15
2x

03-01-15

 

and want to convert it to the below, where the process value becomes the name of the process date... 

 

ID_keyx_process_datey_process_date
101-01-1502-01-15
203-01-15 

 

hope that made sense...

 

what would be the best way to do this ??

Valued Guide
Posts: 860

Re: using column values to become variable names

data have;
infile cards dsd;
informat id_key 1. process $1. process_date mmddyy10.;
format id_key 1. process $1. process_date mmddyy10.;
input ID_key    process$    process_date;
cards;
1,x,01-01-15
1,y,02-01-15
2,x,03-01-15
;run;

proc transpose data=have out=tran_have(drop=process _name_)prefix=Process_Date_;by id_key process;id process;

data want(keep=id_key X_Process_date Y_Process_Date);
set tran_have(rename=process_date_y=Y_Process_date);
by id_key;
retain X_Process_date;
if first.id_key then X_Process_date = Process_Date_X;
if last.id_key;
format X_Process_Date mmddyy10.;
run;

Super Contributor
Posts: 345

Re: using column values to become variable names

Posted in reply to Steelers_In_DC

By using suffix option instead of prefix you can skip the last datastep.

Super User
Posts: 10,035

Re: using column values to become variable names

Check MERGE skill, me , Matt and Arthur.T proposed :

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

 

data have;
infile cards dsd;
informat id_key 1. process $1. process_date mmddyy10.;
format id_key 1. process $1. process_date mmddyy10.;
input ID_key    process$    process_date;
cards;
1,x,01-01-15
1,y,02-01-15
2,x,03-01-15
;
run;
proc sql;
select distinct catt('have(where=(id_key=',id_key,' and process="',process,'") rename=(process_date=',process,'_process_date))') into : merge separated by ' '
 from have;
quit;
data want;
 merge &merge;
 by id_key ;
 drop process;
run;
Super User
Posts: 10,035

Re: using column values to become variable names

 

data have;
infile cards dsd;
informat id_key 1. process $1. process_date mmddyy10.;
format id_key 1. process $1. process_date mmddyy10.;
input ID_key  x  process$    process_date;
cards;
1,1,x,01-01-15
1,2,y,02-01-15
2,1,x,03-01-15
;run;

proc transpose data=have out=tran_have(drop=_name_)  suffix=_Process_Date;
by id_key ;
id process;
var process_date ;
run;

 

Ask a Question
Discussion stats
  • 4 replies
  • 276 views
  • 0 likes
  • 4 in conversation