BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PauloC
Fluorite | Level 6

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:

IDAbrvABCD
35AE2845143.032344692.75
35AI1361450.671101031.6
43AE2074389.86931890.86
43AI1301419.7836559.68

 

I Would like to transpose it like this:

 

IDAE_AAI_AAE_BAI_BAE_CAI_CAE_DAI_D
352841365143.031450.672341104692.751031.6
432071304389.861419.7893361890.86559.68

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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;

View solution in original post

3 REPLIES 3
qoit
Pyrite | Level 9

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__
Jade | Level 19

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
Super User

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: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 997 views
  • 2 likes
  • 4 in conversation