Hello,
I want to transpose a data array.
When the "Script" column contains M2 then I want the "Want" table to report the date in the M2 column.
Another example for the id "789319", "Script" contains M6 I want to report the date in the column "M6"
When there is no data I want it to remain empty
Thanks a lot for your help.
William
Want | |||||||||||
ID | Script | DATE | Flag | ID | M2 | M3 | M4 | M6 | Flag | ||
235638 | Dts_IM2_ras | 23/01/2021 | 0 | 235638 | 23/01/2021 | 24/02/2021 | 26/03/2021 | 27/05/2021 | 0 | ||
235638 | Promo_IM3_be | 24/02/2021 | 0 | 457814 | 25/01/2021 | 28/02/2021 | 29/03/2021 | 30/06/2021 | 1 | ||
457814 | SZE_IM2_rz | 25/01/2021 | 1 | => | 897643 | 31/07/2021 | 0 | ||||
235638 | Queffrti_IM4_rgg | 26/03/2021 | 0 | 894512 | 01/09/2021 | 15/10/2021 | 1 | ||||
235638 | AIU_IM6_ski | 27/05/2021 | 0 | 789319 | 15/12/2021 | 1 | |||||
457814 | alop_IM3_rfz | 28/02/2021 | 1 | ||||||||
457814 | fop_IM4_rhtrh | 29/03/2021 | 1 | ||||||||
457814 | skop_IM6_rrefgr | 30/06/2021 | 1 | ||||||||
897643 | dzd_IM2_rsa | 31/07/2021 | 0 | ||||||||
894512 | uil_IM2_rdzd | 01/09/2021 | 1 | ||||||||
894512 | kujndbe_IM3_ezef | 15/10/2021 | 1 | ||||||||
789319 | kujnzze_IM6_ezff | 15/12/2021 | 1 | ||||||||
Hi,
you could do something like that:
DATA have;
infile datalines TRUNCOVER;
input ID :8. Script :$25. DATE :ddmmyy10. Flag :8.;
format date ddmmyy10.;
datalines4;
235638 Dts_IM2_ras 23/01/2021 0
235638 Promo_IM3_be 24/02/2021 0
457814 SZE_IM2_rz 25/01/2021 1
235638 Queffrti_IM4_rgg 26/03/2021 0
235638 AIU_IM6_ski 27/05/2021 0
457814 alop_IM3_rfz 28/02/2021 1
457814 fop_IM4_rhtrh 29/03/2021 1
457814 skop_IM6_rrefgr 30/06/2021 1
897643 dzd_IM2_rsa 31/07/2021 0
894512 uil_IM2_rdzd 01/09/2021 1
894512 kujndbe_IM3_ezef 15/10/2021 1
789319 kujnzze_IM6_ezff 15/12/2021 1
;;;;
run;
data have;
set have;
length help $10;
array ms M1-M10;
do over ms;
if index(script,vname(ms)) then help=vname(ms);
end;
run;
proc sort data=have;by id flag;run;
proc transpose data=have out=want(drop=_name_);
by id flag;
var date;
id help;
run;
- Cheers -
I would start by extracting the variable name from "script". Depending on the contents of "script" this could work:
varname = substr(scan(Script, 3, '_'), 2);
Next steps:
Post data in usable from, if you want working code.
Hi,
you could do something like that:
DATA have;
infile datalines TRUNCOVER;
input ID :8. Script :$25. DATE :ddmmyy10. Flag :8.;
format date ddmmyy10.;
datalines4;
235638 Dts_IM2_ras 23/01/2021 0
235638 Promo_IM3_be 24/02/2021 0
457814 SZE_IM2_rz 25/01/2021 1
235638 Queffrti_IM4_rgg 26/03/2021 0
235638 AIU_IM6_ski 27/05/2021 0
457814 alop_IM3_rfz 28/02/2021 1
457814 fop_IM4_rhtrh 29/03/2021 1
457814 skop_IM6_rrefgr 30/06/2021 1
897643 dzd_IM2_rsa 31/07/2021 0
894512 uil_IM2_rdzd 01/09/2021 1
894512 kujndbe_IM3_ezef 15/10/2021 1
789319 kujnzze_IM6_ezff 15/12/2021 1
;;;;
run;
data have;
set have;
length help $10;
array ms M1-M10;
do over ms;
if index(script,vname(ms)) then help=vname(ms);
end;
run;
proc sort data=have;by id flag;run;
proc transpose data=have out=want(drop=_name_);
by id flag;
var date;
id help;
run;
- Cheers -
Preparing the data and using TRANSPOSE:
data long;
set have;
vname = substr(scan(script,2,"_"),2,2);
run;
proc sort data=long;
by id;
run;
proc transpose data=long out=want;
by id;
var date;
id vname;
run;
In your data, flag seems to always be constant for a given id, is this also true for your real data?
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: