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-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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