topic Re: Transpose Data in SAS Programming

Transpose Data

NOMBUH — Tue, 17 Dec 2019 07:05:19 GMT

Please Assist:

How to transpose table 1 to table 2

Table 1
	Code	Amount
Name 1	286	R 100
Name 1	687	R 500
Name 2	878	R 250
Name 3	125	R 1 000
Name 3	236	R 980
Name 4	125	R 1 244
Name 4	236	R 1 470

Table 2
	Code_1	Amount_1	Code_2	Amount_2
Name 1	286	R 100	687	R 500
Name 2	878	R 250		R 0
Name 3	125	R 1 000	236	R 980
Name 4	125	R 1 244	236	R 1 470

Thank you.

Re: Transpose Data

Kurt_Bremser — Tue, 17 Dec 2019 07:37:59 GMT

Run a transpose each for code and amount, and merge the two resulting tables.

For code, please provide source data in a data step with datalines.

Re: Transpose Data

ed_sas_member — Tue, 17 Dec 2019 08:23:42 GMT

Hi @NOMBUH

You can do that in a data step using an array:

proc sql noprint;
	select max(total) into: max_obs from (select count(*) as total from table1 group by Name);
quit;

data table2;
	set table1;
	by Name;
	
 	array Code_(&max_obs) $ /* Add $ if Code is a character variable */;
 	array Amount_(&max_obs) $ /* Add $ if Code is a character variable */;
 	
 	if first.Name then do;
		counter=0;
		call missing(of Code_(*));
		call missing(of Amount_(*));
	end;
	
 	counter+1;
 	
 	retain Code_;
 	retain Amount_;

 	
 	do i=1 to &max_obs;
		Code_(counter)=Code;
		Amount_(counter)=Amount;
	end;
	
	if last.Name then output;
	drop Code Amount counter i;

run;

You can also use a proc transpose, as suggested by @Kurt_Bremser :

proc transpose data=table1 out=table1_tr_code (drop=_name_) prefix=Code_;
	var Code;
	by Name;
run;

proc transpose data=table1 out=table1_tr_amount (drop=_name_) prefix=Amount_;
	var Amount;
	by Name;
run;

data table2;
	merge table1_tr_code table1_tr_amount;
	by Name;
run;

Re: Transpose Data

NOMBUH — Tue, 17 Dec 2019 08:31:11 GMT

Data Table_1;
Input Name$ code$ Amount;
cards;
John 2 100
John 6 500
Lucia 8 250
Josh 1 1000
Josh 2 980
Tim 1 1244
Tim 2 1470
;

run;

Re: Transpose Data

NOMBUH — Tue, 17 Dec 2019 08:36:24 GMT

Thank you very much 🙂 🙂

Re: Transpose Data

ed_sas_member — Tue, 17 Dec 2019 09:44:09 GMT

You're welcome!
Could you please mark the topic as answered?
Thank you 🙂

Re: Transpose Data

Ksharp — Tue, 17 Dec 2019 11:21:15 GMT

Data Table_1;
Input Name$ code$ Amount;
cards;
John 2 100
John 6 500
Lucia 8 250
Josh 1 1000
Josh 2 980
Tim 1 1244
Tim 2 1470
;

run;

proc sql noprint;
select max(n) into : n
 from ( select count(*) as n from table_1 group by name );
quit;
proc summary data=table_1 nway;
class name;
output out=want idgroup(out[&n] (code amount)=);
run;