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;
