Transpose an array

[WilliamB]

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

[andreas_lds]

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:

• create a sorted list (in a macro-variable) of all m-variables to be created
• sort data by id
• maybe: use a data step, retain the m-variables, use first/last to fill the variables, this is tricky due to the non existence of m1 and m5 in your data, the appropriate position in the array could be returned by i = findw("&varlist", varname, ' ', 'set');

Post data in usable from, if you want working code.

[Oligolas · Accepted solution]

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;

[Kurt_Bremser]

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?