Hi,
I'm looking to transpose my data. I've attached the file for reference. There are around 17 odd columns & 171974 rows. I need to transpose this information, such that my rows end up in the columns & my columns in my rows.
Can someone please help me with the syntax?
I need this urgently and hence, it would be great if you can share it asap.
Cheers,
Nimish
Did you try PROC TRANSPOSE?
proc transpose data=have out=want;
run;
Hi Tom,
I did try proc transpose, but it hasn't helped. I also added the by variable, but it hasn't helped. Can you use the data set at your end and test the transpose?
Thanks and Regards,
Nimish
Hi:
I tested Tom's code with just 10 rows in a DATALINES section, so anyone could run the code and Tom's code worked for me. If you are having problems with your data, either you are having an issue reading the Excel file or you are having an issue with PROC TRANSPOSE. Are there error messages in the SAS Log? What code have you tried? What is your Log?
My first step (the DATA step to read 10 rows) would obviously be replaced by your code to read the Excel file. I rarely post code to read Excel files because I only have SAS 9.4 and my SAS and Office might not match anybody else's SAS and Office or "bitness" to write the correct PROC IMPORT or LIBNAME statement. So if I did post code, the chances are good that someone else might not be able to run my code. But with this, as proof of concept, I have demonstrated that Tom's suggestion is the correct suggestion, assuming that you do in fact want to create a dataset of 17 rows and over 17,000 columns.
Another possible structure, might be one like this:
varname grp varvalue
p1 1 379
p2 1 0
p3 1 0
p4 1 379
p5 1 0
p6 1 379
p7 1 379
p8 1 379
p9 1 759
p10 1 379
p11 1 0
p12 1 0
p13 1 0
p14 1 0
p15 1 0
p16 1 0
p17 1 379
p1 2 0
p2 2 331
p3 2 0
p4 2 331
p5 2 0
p6 2 664
p7 2 332
p8 2 662
p9 2 0
p10 2 0
p11 2 331
p12 2 332
p13 2 0
p14 2 332
p15 2 0
p16 2 0
p17 2 0
However, your original post said that you wanted a transpose "such that my rows end up in the columns & my columns in my rows." This might be the kind of issue that you want to raise with Tech Support. Depending on the type of error message you are getting, they may be the only ones to help.
Cynthia
data have;
informat p1-p17 comma.;
infile datalines dlm=',' dsd;
input P1 P2 P3 P4 P5 P6 P7 P8 P9 P10 P11 P12 P13 P14 P15 P16 P17;
return;
datalines;
379,0,0,379,0,379,379,379,759,379,0,0,0,0,0,0,379
0,331,0,331,0,664,332,662,0,0,331,332,0,332,0,0,0
332,0,0,0,332,0,564,0,0,0,0,332,331,332,0,0,332
0,0,0,331,0,0,0,0,0,0,332,0,0,0,0,0,663
0,331,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
0,0,332,0,0,664,563,0,0,0,0,0,0,0,0,0,0
0,0,0,0,0,0,0,0,332,0,332,332,0,332,0,0,0
"1,091","3,126","2,371","1,519","1,897","4,936","3,036","6,416","4,933","5,674","5,316","5,646","3,796","5,827","8,248","6,401","6,352"
"3,033","1,753","1,374","1,849","3,417","2,268","2,659","4,555","2,278","3,419","2,278","7,969","2,278","4,882","5,264","3,791","2,656"
"4,835","4,031","3,622","2,988","4,935","9,871","4,446","6,452","8,349","7,944","8,734","12,902","8,727","11,326","11,994","9,433","6,024"
;
run;
proc transpose data=have out=want;
run;
ods listing;
proc print data=want;
title 'After Proc Transpose';
run;
What did you try? Did you get an error message? If you did not get what you want then how is it wrong?
What BY variable? Your example sheet just had 17 variables labeled P1-P17, none looks like a BY variable from the name or for that matter the values.
If I just take the first 5 columns and 9 rows of the data in your sheet you can see how transpose will work.
data tall ;
input p1-p5;
cards;
379 0 0 379 0
0 331 0 331 0
332 0 0 0 332
0 0 0 331 0
0 331 0 0 0
0 0 332 0 0
0 0 0 0 0
1091 3126 2371 1519 1897
3033 1753 1374 1849 3417
run;
proc transpose data=tall out=wide(drop=_name_);
run;
data _null_;
set wide;
put (_all_) (:);
run;
379 0 332 0 0 0 0 1091 3033
0 331 0 0 331 0 0 3126 1753
0 0 0 0 0 332 0 2371 1374
379 331 0 331 0 0 0 1519 1849
0 0 332 0 0 0 0 1897 3417
NOTE: There were 5 observations read from the data set WORK.WIDE.
Hi:
I am just having a hard time visualizing why you want a dataset of 17 rows and over 17,000 columns -- are you creating a report? Or do you really want a table with that many columns?
Just curious.
cynthia
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: