Proc Transpose and keep the first column

[PauloC]

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

[qoit]

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

[data_null__]

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

---

[Ksharp · Accepted solution]

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;