topic Re: How to import a CSV file that is transposed format in SAS Programming

How to import a CSV file that is transposed format

Reeza — Tue, 21 Jul 2015 17:45:44 GMT

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!

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

Tom — Tue, 21 Jul 2015 18:07:55 GMT

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;

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

ballardw — Tue, 21 Jul 2015 19:36:10 GMT

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.

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

Reeza — Tue, 21 Jul 2015 20:18:33 GMT

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

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

Patrick — Tue, 21 Jul 2015 21:30:04 GMT

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

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

Reeza — Tue, 21 Jul 2015 21:41:35 GMT

@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

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

Patrick — Tue, 21 Jul 2015 21:49:26 GMT

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

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

art297 — Tue, 21 Jul 2015 22:32:52 GMT

: 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

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

Reeza — Tue, 21 Jul 2015 23:36:45 GMT

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:

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

art297 — Tue, 21 Jul 2015 23:59:06 GMT

: Okay, my SAS friends might like me again

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

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

Reeza — Wed, 22 Jul 2015 00:12:20 GMT

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

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

Tom — Wed, 22 Jul 2015 01:01:28 GMT

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;