topic Re: Proc transpose duplicates in SAS Programming

Proc transpose duplicates

Pradeepbanu — Fri, 28 Aug 2020 05:34:02 GMT

I have data like below

ID	Custom_flag	Variable
1000	H1_Curr	1
1000	H2_Curr	1
1000	H1_Prev	1
2000	H1_Curr	1
2000	H2_Prev	1
2000	H2_Curr	1

I need Output like

ID	H1_Curr	H2_Curr	H1_Prev	H2_Prev
1000	1	1	1
2000	1	1		1

I am doing transpose like this

proc sort data= bef_have out=bef_have_sort NODUPKEY;
by custom_flag ID;
run; 

proc transpose data= bef_have_sort out = want_data(drop=_name_ _label_);
by id;
id custom_flag;
var variable;
run;


I am getting output like below

ID	H1_Curr	H2_Curr	H1_Prev	H2_Prev
1000	1
1000		1
1000			1	1
2000	1
2000		1
2000				1

I am not sure why this is happening! Help is much appreciated1

Re: Proc transpose duplicates

andreas_lds — Fri, 28 Aug 2020 05:52:45 GMT

I am getting an error when i try to execute the proc transpose, sorting by Id and custom_flag should solve the problem.

Re: Proc transpose duplicates

Kurt_Bremser — Fri, 28 Aug 2020 06:12:43 GMT

Works fine for me:

data have;
input ID $ Custom_flag $ Variable;
datalines;
1000  H1_Curr 1
1000  H2_Curr 1
1000  H1_Prev 1
2000  H1_Curr 1
2000  H2_Prev 1
2000  H2_Curr 1
;

proc transpose data=have out=want (drop=_name_);
by id;
id custom_flag;
var variable;
run;

proc print data=want noobs;
run;

Result:

ID	H1_Curr	H2_Curr	H1_Prev	H2_Prev
1000	1	1	1	.
2000	1	1	.	1