DATA Step, Macro, Functions and more

Transpose

Reply
Occasional Contributor
Posts: 14

Transpose

Hi Friends,

 

I have requirement for transposing the data from Rows to columns. Please find the attched excel sheet for your reference.

Input sheet is the data can be used for input and output data is what i am expecting. Please help.

Super User
Posts: 17,826

Re: Transpose

That's an ugly format. Is this for a report or do you need a dataset? 

 

The quickest way, least code is two proc transposes. 

First transform your data to a long dataset, then you can re-transform to a wide dataset using different heading groups. 

Occasional Contributor
Posts: 14

Re: Transpose

please check the latest file uploaded

Frequent Contributor
Posts: 103

Re: Transpose

[ Edited ]

Hi, quick and dirty for this particular case:

DATA input;
   length name $1 place $4 qty1 qty2 qty3 3;
   input name place qty1 qty2 qty3;
   datalines;
   A USA1 1 2 3 
   A USA1 1 2 3 
   A USA1 1 2 3 
   A USA1 1 2 3 
   A USA1 1 2 3 
   B USA1 1 2 3 
   B USA1 1 2 3 
   B USA1 1 2 3 
   B USA1 1 2 3 
   B USA1 1 2 3 
   B USA1 1 2 3 
   B USA1 1 2 3
   ;
RUN;

PROC SORT data=input; by name place;RUN;
DATA input;
   length order 8;
   set input;
   retain order;
   by name place;
   if first.name then order=0;
   order+1;
RUN;

PROC SQL;
CREATE TABLE OUTPUT AS SELECT a.name, a.place, a.qty1, a.qty2, a.qty3, b.name AS nameb, b.place AS placeb, b.qty1 AS qty1b, b.qty2 AS qty2b, b.qty3 AS qty3b FROM input(WHERE=(name eq 'A')) a FULL OUTER JOIN input(WHERE=(name eq 'B')) b ON a.order eq b.order AND a.place eq b.place ; QUIT;
________________________

- That still only counts as one -

Super User
Posts: 9,681

Re: Transpose

Plz post data at here . No one would like to download a file .

Check MERGE skill proposed by Me,Matt,Arthur.T :
http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf



data have;
infile cards expandtabs truncover;
input Name $	Place & $	Qty1 	Qty2	Qty3;
cards;
A	USA 1	1	2	3
A	USA 1	1	2	3
A	USA 1	1	2	3
A	USA 1	1	2	3
A	USA 1	1	2	3
B	USA 1	1	2	3
B	USA 1	1	2	3
B	USA 1	1	2	3
B	USA 1	1	2	3
B	USA 1	1	2	3
B	USA 1	1	2	3
B	USA 1	1	2	3
;
run;
proc sql ;
select distinct catt('have(where=(name="',name,'")
 rename=(qty1=qty1_',name,' qty2=qty2_',name,' qty3=qty3_',name,') )')
 into : merge separated by ' ' 
 from have
  order by 1 desc;
quit;

data want;
 merge &merge ;
 by place;
 output;
 call missing(of _all_);
run;





Occasional Contributor
Posts: 14

Re: Transpose

Hello Ksharp,

 

Your solutions fits exactly as i expected. Only thing is i have some problem with catx function. when i am using fpr morethan 5 qty variables i am getting below warnign generated and the output also not as expected.

 

 

WARNING: In a call to the CATT function, the buffer allocated for the result was not long enough to contain the concatenation of
all the arguments. The correct result would contain 219 characters, but the actual result might either be truncated to 200
character(s) or be completely blank, depending on the calling environment. The following note indicates the left-most
argument that caused truncation

Super User
Posts: 9,681

Re: Transpose

You could try define a larger length than 200:





data have;
infile cards expandtabs truncover;
input Name $	Place & $	Qty1 	Qty2	Qty3;
cards;
A	USA 1	1	2	3
A	USA 1	1	2	3
A	USA 1	1	2	3
A	USA 1	1	2	3
A	USA 1	1	2	3
B	USA 1	1	2	3
B	USA 1	1	2	3
B	USA 1	1	2	3
B	USA 1	1	2	3
B	USA 1	1	2	3
B	USA 1	1	2	3
B	USA 1	1	2	3
;
run;
proc sql ;
select distinct catt('have(where=(name="',name,'")
 rename=(qty1=qty1_',name,' qty2=qty2_',name,' qty3=qty3_',name,') )')
 as merge length=400
 into : merge separated by ' '
 from have
  order by 1 desc;
quit;

data want;
 merge &merge ;
 by place;
 output;
 call missing(of _all_);
run;
Super User
Posts: 10,500

Re: Transpose

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

Occasional Contributor
Posts: 6

Re: Transpose

[ Edited ]
proc sort data=have;
	by place name qty:;
run;

data have1;
	retain id 0;
	set have;
	by place name;

	if first.name then
		id=1;
	else id=id+1;
run;

data have2;
	set have1;
	array ar_name (*) Qty:;
	do i=1 to dim(ar_name);
		new_col=cat(vname(ar_name[i]),'_',name);
		new_val=ar_name[i];
		output; /*put new_col= new_val=;*/
	end;

	drop i QTY: name;
run;

proc sort data=have2;
	by id new_col place;
run;

proc transpose data=have2 out=final(drop=id _NAME_);
	id new_col;
	by id place;
	var new_val;
run;
Ask a Question
Discussion stats
  • 8 replies
  • 840 views
  • 0 likes
  • 6 in conversation