topic Re: How to transform the columns into rows by using external csv file? in SAS Procedures

How to transform the columns into rows by using external csv file?

Praveen_G — Thu, 20 Jun 2013 12:42:44 GMT

Hello,

Please find the below screen shot, In particular, all the columns G to the end are the same. I need to transform the columns into rows. For each row read in, I need to output a row for each column (G-the end). I then need to convert each file back into a CSV, It should be "If units > 0 then Output" to only create rows for cells with data .So please kindly advise how to proceed.

Thanks & Regards,

Praveen

The final output should be in below format

Re: How to transform the columns into rows by using external csv file?

Scott_Mitchell — Mon, 08 Jul 2013 12:40:46 GMT

Can you please copy the data in the csv file using notepad? I am not prepared to retype the screenshots you have attached.

Re: How to transform the columns into rows by using external csv file?

yifengwan — Mon, 08 Jul 2013 13:25:12 GMT

you may use the command below to transform the file. But first you have to import the .csv file into SAS

PROC TRANSPOSE <DATA=input-data-set>

<DELIMITER=delimiter>

<LABEL=label>

<LET>

<NAME=name>

<OUT=output-data-set>

<PREFIX=prefix>

<SUFFIX=suffix>;

BY <DESCENDING> variable-1

<...<DESCENDING> variable-n>;

COPY variable(s);

ID variable;

IDLABEL variable;

VAR variable(s);

Re: How to transform the columns into rows by using external csv file?

esjackso — Mon, 08 Jul 2013 13:51:11 GMT

They are a couple unanswered questions that you dont really cover in your post. And your sample data and output doesnt really answer either, since the input does not match the outpu (ie Kansas output is listed as 76 while only 8 appear in the input).

There are three codes for each input line ... so do you want each code to be on its own observation in the output with number of under the zipcode applied to each?

Im assuming the total variable on the input is not relevant to the output desired.

Im guessing you might have more challenging of time reading in your source if the screen shot is in fact the way the csv is structured (the first three rows are horizontally structured while the remaining are vertically structured).

If you can provide more explanation and perhaps an actual sample input file it would help tailor the code to your actual situation.