Help using Base SAS procedures

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

Reply
N/A
Posts: 1

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

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


Super Contributor
Posts: 297

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

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

Occasional Contributor
Posts: 8

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

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

Super Contributor
Posts: 333

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

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.

EJ

Ask a Question
Discussion stats
  • 3 replies
  • 275 views
  • 0 likes
  • 4 in conversation