topic Re: using column values to become variable names in SAS Programming

using column values to become variable names

devindaj — Tue, 12 Jan 2016 04:28:15 GMT

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

ID_key	process	process_date
1	x	01-01-15
1	y	02-01-15
2	x	03-01-15

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

ID_key	x_process_date	y_process_date
1	01-01-15	02-01-15
2	03-01-15

hope that made sense...

what would be the best way to do this ??

Re: using column values to become variable names

Steelers_In_DC — Tue, 12 Jan 2016 14:08:51 GMT

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;

Re: using column values to become variable names

andreas_lds — Tue, 12 Jan 2016 21:33:37 GMT

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

Re: using column values to become variable names

Ksharp — Wed, 13 Jan 2016 02:40:50 GMT

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;

Re: using column values to become variable names

Ksharp — Wed, 13 Jan 2016 02:52:27 GMT

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;