Help using Base SAS procedures

Proc transpose

Reply
Occasional Contributor
Posts: 18

Proc transpose

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

Super User
Super User
Posts: 7,035

Re: Proc transpose

Posted in reply to Nimish_Vaddiparti

Did you try PROC TRANSPOSE?

proc transpose data=have out=want;

run;

Occasional Contributor
Posts: 18

Re: Proc transpose

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

SAS Super FREQ
Posts: 8,861

Re: Proc transpose

Posted in reply to Nimish_Vaddiparti

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;

Super User
Super User
Posts: 7,035

Re: Proc transpose

Posted in reply to Nimish_Vaddiparti

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_) (Smiley Happy;

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.

SAS Super FREQ
Posts: 8,861

Re: Proc transpose

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

Ask a Question
Discussion stats
  • 5 replies
  • 342 views
  • 1 like
  • 3 in conversation