topic Re: Transpose in SAS Programming

Transpose

sascodequestion — Fri, 09 Sep 2016 09:50:22 GMT

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.

Re: Transpose

Reeza — Fri, 09 Sep 2016 09:57:34 GMT

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.

Re: Transpose

sascodequestion — Fri, 09 Sep 2016 10:14:58 GMT

please check the latest file uploaded

Re: Transpose

Oligolas — Fri, 09 Sep 2016 11:01:26 GMT

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;

Re: Transpose

Ksharp — Fri, 09 Sep 2016 10:23:40 GMT

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;

Re: Transpose

sascodequestion — Fri, 09 Sep 2016 13:39:02 GMT

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

Re: Transpose

ballardw — Fri, 09 Sep 2016 14:55:37 GMT

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.

Re: Transpose

Michiel — Fri, 09 Sep 2016 22:27:05 GMT

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;

Re: Transpose

Ksharp — Sat, 10 Sep 2016 02:19:14 GMT

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;