topic Re: Proc transpose in SAS Procedures

Proc transpose

Nimish_Vaddiparti — Mon, 28 Jul 2014 23:13:12 GMT

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

Re: Proc transpose

Tom — Tue, 29 Jul 2014 00:26:24 GMT

Did you try PROC TRANSPOSE?

proc transpose data=have out=want;

run;

Re: Proc transpose

Nimish_Vaddiparti — Tue, 29 Jul 2014 01:19:38 GMT

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

Re: Proc transpose

Cynthia_sas — Tue, 29 Jul 2014 02:59:13 GMT

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

Re: Proc transpose

Cynthia_sas — Tue, 29 Jul 2014 03:19:34 GMT

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;

Re: Proc transpose

Tom — Tue, 29 Jul 2014 04:48:32 GMT

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.