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:

 Name Alfred Alice Barbara Carol Henry James Jane Janet Jeffrey John Joyce Judy Louise Mary Philip Robert Ronald Thomas William Sex M F F F M M F F M M F F F F M M M M M Age 14 13 13 14 14 12 12 15 13 12 11 14 12 15 16 12 15 11 15 Height 69 56.5 65.3 62.8 63.5 57.3 59.8 62.5 62.5 59 51.3 64.3 56.3 66.5 72 64.8 67 57.5 66.5 Weight 112.5 84 98 102.5 102.5 83 84.5 112.5 84 99.5 50.5 90 77 112 150 128 133 85 112

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

Thanks!

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.

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.

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

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

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

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

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.

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;

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