BookmarkSubscribeRSS Feed
Reeza
Super User

The data structure of the file is transposed - all variable names are in the first row and the data are in the columns

For example this is what SASHELP.CLASS would look like:

NameAlfredAliceBarbaraCarolHenryJamesJaneJanetJeffreyJohnJoyceJudyLouiseMaryPhilipRobertRonaldThomasWilliam
SexMFFFMMFFMMFFFFMMMMM
Age14131314141212151312111412151612151115
Height6956.565.362.863.557.359.862.562.55951.364.356.366.57264.86757.566.5
Weight112.58498102.5102.58384.5112.58499.550.5907711215012813385112

The observations are comma delimited and we can assume that there is the same number of columns for every record.

If it was a smaller file I would read it all in as character and transpose but there are over 500 columns in this file.

Fields with embedded comma's are quoted and it looks like the length of some lines is well over 200K.

And because I know someone will mention it, no, I can't request the data in a different format at this time Smiley Happy

Thanks!

11 REPLIES 11
Tom
Super User Tom
Super User

Just use PROC TRANPOSE.  You might need to add some more logic to determine which variables should be numeric instead of character.

data have ;

   infile csv dsd truncover lrecl=2000000 length=len column=ccol;

   column+1;

   length _name_ $32 ;

   input _name_ @ ;

   do observation=1 by 1 until(ccol > len) ;

     length value $200 ;

     input value @;

     output;

   end;

run;

proc print;

run;

proc sort;

  by observation column ;

run;

proc transpose data=have out=want ;

  by observation ;

  id _name_;

  var value ;

run;

proc print;

run;

ballardw
Super User

If this were a one-time thing I would be tempted to try the Excel copy and paste transpose, save as CSV and import that result.

Reeza
Super User

It's not...and unfortunately they can add/change variables as well. 

Patrick
Opal | Level 21

I believe %transpose macro could make your live easier. I found only this paper http://support.sas.com/resources/papers/proceedings13/538-2013.pdf but I believe there is a newer and improved version available "somewhere" (Art will know).

Reeza
Super User

@patrick I'll give that a try but I currently don't see how that helps over a proc transpose...but I'm having issues with this one for sure.

Latest version of code is on SasCommunity

A Better Way to Flip (Transpose) a SAS Dataset - sasCommunity

Patrick
Opal | Level 21

I believe it deals better with maintaining character/numeric variables when transposing.

art297
Opal | Level 21

: First, thank you for finding the link to the paper that suggested (BTW: all of my papers can be found at: Presentations:Art297 Papers and Presentations - sasCommunity. I'll likely lose some of my friends at SAS for what I'm going to suggest, but here is how I think the problem could be solved most easily: TAKE ADVANTAGE OF EXCEL'S TRANSPOSE FEATURE.

I solved the problem by bring up the file in excel, highlighting and copying all of the rows, moving my cursor down to the first cell under the original file and then clicking that cell. Then, from the home menu, I clicked on PASTE and chose the last icon in the first (paste) section (transpose).

Then I deleted all of the original rows and saved the file as a csv file.

Then I ran the following code:

proc import datafile="c:\art\SASHELP_CLASS2_Transposed.csv" out=want replace dbms=csv;

  getnames=yes;

  datarow=2;

  guessingrows=32767;

run;

Art, CEO, AnalystFinder.com

Reeza
Super User

I would have to read all as character initially, so that wouldn't help Smiley Sad

That's what we're doing right now, the full process is in Excel. It's hard to say SAS is better if I have to keep using Excel for parts. :smileyblush:

art297
Opal | Level 21

: Okay, my SAS friends might like me again Smiley Happy

How about:

proc import datafile="c:\art\SASHELP_CLASS_Transposed.csv" out=have replace dbms=csv;

  getnames=yes;

  datarow=2;

  guessingrows=32767;

run;

proc transpose data=have out=need (drop=_label_);

  var _all_;

run;

proc export data=need file='c:\art\SASHELP_CLASS2_Transposed.csv' replace dbms=csv;

  putnames=no;

run;

proc import datafile="c:\art\SASHELP_CLASS2_Transposed.csv" out=want replace dbms=csv;

  getnames=yes;

  datarow=2;

  guessingrows=32767;

run;

Art, CEO, AnalystFinder.com

Reeza
Super User

Brilliant! I think that will work and I'll give it a try tomorrow Smiley Happy

Tom
Super User Tom
Super User

If you are willing to let PROC IMPORT guess at the variable types you can "transpose" the data without using PROC TRANSPOSE or EXCEL.

filename IN 'c:\downloads\in.csv';

filename OUT 'c:\downloads\out.csv';

data have ;

   infile IN dsd truncover lrecl=2000000 length=len column=ccol;

   length row column 8 value $200 ;

   row+1;

   do column=1 by 1 until(ccol > len) ;

     input value @;

     output;

   end;

run;

proc sort;

  by column row ;

run;

data _null_;

  set have ;

  by column ;

  file OUT dsd lrecl=2000000 ;

  put value @;

  if last.column then put ;

run;

proc import datafile=OUT out=want dbms=dlm replace;

  delimiter=',';

run;

proc print;

run;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 11 replies
  • 3082 views
  • 7 likes
  • 5 in conversation