Hello! I am trying to transpose a table keeping the first column. I would appreciate help! Thank you in advance!
I have this data set:
ID | Abrv | A | B | C | D |
35 | AE | 284 | 5143.03 | 234 | 4692.75 |
35 | AI | 136 | 1450.67 | 110 | 1031.6 |
43 | AE | 207 | 4389.86 | 93 | 1890.86 |
43 | AI | 130 | 1419.78 | 36 | 559.68 |
I Would like to transpose it like this:
ID | AE_A | AI_A | AE_B | AI_B | AE_C | AI_C | AE_D | AI_D |
35 | 284 | 136 | 5143.03 | 1450.67 | 234 | 110 | 4692.75 | 1031.6 |
43 | 207 | 130 | 4389.86 | 1419.78 | 93 | 36 | 1890.86 | 559.68 |
The simplest way is double proc transpose .
data have;
infile datalines pad;
input id $ abrv $ A B C D;
datalines;
35 AE 284 5143.03 234 4692.75
35 AI 136 1450.67 110 1031.6
43 AE 207 4389.86 93 1890.86
43 AI 130 1419.78 36 559.68
;
run;
proc transpose data=have out=temp;
by id abrv;
var a b c d;
run;
proc transpose data=temp out=want delimiter=_;
by id;
id abrv _name_;
var col1;
run;
Hope the below helps:
data have;
infile datalines pad;
input id $ abrv $ A B C D;
datalines;
35 AE 284 5143.03 234 4692.75
35 AI 136 1450.67 110 1031.6
43 AE 207 4389.86 93 1890.86
43 AI 130 1419.78 36 559.68
;
run;
%macro want;
%macro _;
%mend _;
%local vars i;
%let vars = a b c d;
data want_ae;
set have (where=(abrv = 'AE'));
array ae{4} ae_a ae_b ae_c ae_d;
%do i = 1 %to 4;
ae{&i.} = %qscan(&vars.,&i.,%str( ));
%end;
drop a b c d abrv;
run;
data want_ai;
set have (where=(abrv = 'AI'));
array ai{4} ai_a ai_b ai_c ai_d;
%do i = 1 %to 4;
ai{&i.} = %qscan(&vars.,&i.,%str( ));
%end;
drop a b c d abrv;
run;
data want;
merge want_ae want_ai;
by id;
run;
proc sql;
drop table want_ae;
drop table want_ai;
%mend;
%want
What does this do? I don't see where it was called.
%macro _;
%mend _;
@qoit wrote:
Hope the below helps:
data have; infile datalines pad; input id $ abrv $ A B C D; datalines; 35 AE 284 5143.03 234 4692.75 35 AI 136 1450.67 110 1031.6 43 AE 207 4389.86 93 1890.86 43 AI 130 1419.78 36 559.68 ; run; %macro want; %macro _; %mend _; %local vars i; %let vars = a b c d; data want_ae; set have (where=(abrv = 'AE')); array ae{4} ae_a ae_b ae_c ae_d; %do i = 1 %to 4; ae{&i.} = %qscan(&vars.,&i.,%str( )); %end; drop a b c d abrv; run; data want_ai; set have (where=(abrv = 'AI')); array ai{4} ai_a ai_b ai_c ai_d; %do i = 1 %to 4; ai{&i.} = %qscan(&vars.,&i.,%str( )); %end; drop a b c d abrv; run; data want; merge want_ae want_ai; by id; run; proc sql; drop table want_ae; drop table want_ai; %mend; %want
The simplest way is double proc transpose .
data have;
infile datalines pad;
input id $ abrv $ A B C D;
datalines;
35 AE 284 5143.03 234 4692.75
35 AI 136 1450.67 110 1031.6
43 AE 207 4389.86 93 1890.86
43 AI 130 1419.78 36 559.68
;
run;
proc transpose data=have out=temp;
by id abrv;
var a b c d;
run;
proc transpose data=temp out=want delimiter=_;
by id;
id abrv _name_;
var col1;
run;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.