topic Re: Transpose one column into multiple columns in SAS Programming

Transpose one column into multiple columns

Chira85 — Mon, 11 Jan 2021 15:31:20 GMT

Dear all,

I want to transpose this column structure from round 1 to n:

round1

newr

value1

newg

value2

newg

value3

to the following one:

Round newer newg newg

1 value1 value2 value3

Thank you for any help!

Re: Transpose one column into multiple columns

ballardw — Mon, 11 Jan 2021 15:40:52 GMT

Please provide your data example in the form of a data step. Please note: SAS will not allow you to have two variables with the same name in a data set. So if "newg" is supposed to be a variable your want is not possible.

Are there any other variables involved? If so you need to show what happens with them.

You also need to show at least 2 and better 3 "examples" so we can tell what some of the "n" rows of data start with. Such things like is it always exactly 3 sets of values?

Also, make sure that what you show is what you mean. The message windows on this forum will reformat text and sometimes what is in the window doesn't quite match what was intended.

Re: Transpose one column into multiple columns

Chira85 — Mon, 11 Jan 2021 15:56:18 GMT

Thank you for your response. The data I'am going to transpose is an output from another fortran program. Newg represents 2 variables. The data is:

data transpose;

input iteration;

cards;

round1

newr

value1

newg

value2

newg

value3

round2

newr

value4

newg

value5

newg

value6

round3

newr

value7

newg

value8

newg

value9

;

run;

Re: Transpose one column into multiple columns

ballardw — Mon, 11 Jan 2021 18:00:35 GMT

So you do not actually have a SAS data set yet apparently.

To read the text output that look like that,

assuming you always have exactly 3 groups of values you can read it with something similar to the following code.
The EXACTLY part is important as the / in the input statement below tells SAS to read the next value on the next line. Since SAS cannot have variables with the same name this skips past the newr and newg values as essentially meaningless and counts the rounds. You have have not specified if the "value" are supposed to be character or numeric. The $ in the input indicates to read the data as character. If the values are actually numeric then you remove the $.

data transpose;
   infile "path to your text file" ;
   Retain Round 1;
   input /
         /
         newr $
         /
         /
         newg1 $
         /
         /
         newg2 $
   ;
   output;
   round+1;
 
cards;
round1
newr
value1
newg
value2
newg
value3
round2
newr
value4
newg
value5
newg
value6
round3
newr
value7
newg
value8
newg
value9
;
run;

If you are writing the FORTRAN I would strongly suggest using ONE ouput to write the value of Round and the three other variables on one line as that makes much more sense in general.

Re: Transpose one column into multiple columns

Chira85 — Tue, 12 Jan 2021 09:26:00 GMT

Thank you very much for your help. It works perfectly!