BookmarkSubscribeRSS Feed
Nimish_Vaddiparti
Calcite | Level 5

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

5 REPLIES 5
Tom
Super User Tom
Super User

Did you try PROC TRANSPOSE?

proc transpose data=have out=want;

run;

Nimish_Vaddiparti
Calcite | Level 5

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

Cynthia_sas
SAS Super FREQ

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;

Tom
Super User Tom
Super User

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.

Cynthia_sas
SAS Super FREQ

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1087 views
  • 1 like
  • 3 in conversation