BookmarkSubscribeRSS Feed
sascodequestion
Fluorite | Level 6

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.

8 REPLIES 8
Reeza
Super User

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. 

sascodequestion
Fluorite | Level 6

please check the latest file uploaded

Oligolas
Barite | Level 11

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;
________________________

- Cheers -

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





sascodequestion
Fluorite | Level 6

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

Ksharp
Super User
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;
ballardw
Super User

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.

Michiel
Fluorite | Level 6
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 8 replies
  • 1953 views
  • 0 likes
  • 6 in conversation