DATA Step, Macro, Functions and more

How to import a CSV file that is transposed format

Reply
Super User
Posts: 19,850

How to import a CSV file that is transposed format

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!

Attachment
Super User
Super User
Posts: 7,071

Re: How to import a CSV file that is transposed format

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;

Super User
Posts: 11,343

Re: How to import a CSV file that is transposed format

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.

Super User
Posts: 19,850

Re: How to import a CSV file that is transposed format

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

Respected Advisor
Posts: 4,173

Re: How to import a CSV file that is transposed format

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).

Super User
Posts: 19,850

Re: How to import a CSV file that is transposed format

@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

Respected Advisor
Posts: 4,173

Re: How to import a CSV file that is transposed format

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

PROC Star
Posts: 7,487

Re: How to import a CSV file that is transposed format

: 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

Super User
Posts: 19,850

Re: How to import a CSV file that is transposed format

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:

PROC Star
Posts: 7,487

Re: How to import a CSV file that is transposed format

: 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

Super User
Posts: 19,850

Re: How to import a CSV file that is transposed format

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

Super User
Super User
Posts: 7,071

Re: How to import a CSV file that is transposed format

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;

Ask a Question
Discussion stats
  • 11 replies
  • 803 views
  • 7 likes
  • 5 in conversation