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.
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;
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.
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;
Thank you very much 🙂 🙂
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.