topic Re: How to transform a column of a file in different lines in SAS Programming

How to transform a column of a file in different lines

Maider — Mon, 29 Oct 2018 19:42:03 GMT

I have a DO loop where I obtain a value and I write it in a different file, so I obtain something like this:

1 0
1 63
1 41
2 39
2 84
2 105
3 87
3 117
3 105

4 147
4 74
4 14

How can I separate this second column in different lines to obtain another file like this:

1 0 63 41
2 39 84 105

3 87 117 105

4 147 74 14

Thank you

Re: How to transform a column of a file in different lines

novinosrin — Mon, 29 Oct 2018 19:45:02 GMT

data have;
input var var2;
cards;
1 0
1 63
1 41
2 39
2 84
2 105
3 87
3 117
3 105
4 147
4 74
4 14
;

proc transpose data=have out=want;
by var;
var var2;
run;

straight forward proc transpose

Re: How to transform a column of a file in different lines

mkeintz — Mon, 29 Oct 2018 21:32:27 GMT

If it's raw data and always comes in 3-line sets for each id, you can use some of the features of the INPUT statement:

data have (drop=_:);
  input var1 var2_1 / _dummy var2_2 / _dummy var2_3;
cards;
1 0
1 63
1 41
2 39
2 84
2 105
3 87
3 117
3 105
4 147
4 74
4 14
;

The '/' tells the input statement to go to the next line. The variable _DUMMY, which is dropped from the output, is read as the first value of lines 2 and 3.

If it's already a SAS data set, then use the proc transpose as per @novinosrin's suggestion.

Re: How to transform a column of a file in different lines

ballardw — Mon, 29 Oct 2018 23:48:51 GMT

@Maider wrote:

I have a DO loop where I obtain a value and I write it in a different file, so I obtain something like this:

1 0
1 63
1 41
2 39
2 84
2 105
3 87
3 117
3 105

4 147
4 74
4 14

How can I separate this second column in different lines to obtain another file like this:

1 0 63 41
2 39 84 105

3 87 117 105

4 147 74 14

Thank you

It would really help to show the code you used as it is very likely to be a simple modification of that.

By "file" do you mean a SAS data set or an external text file or similar?