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.
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.
please check the latest file uploaded
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 -
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;
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
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;
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.
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.