topic Re: Change columns to observations in SAS Programming

Change columns to observations

Jason2020 — Mon, 12 Nov 2018 19:13:26 GMT

Hi,

I have several data sets that look like:

Obs v1 v2 v3

1 2 3 3

2 5 3 2

3 1 0 3

etc

;

And would like to move the columns to rows:

Obs v

1 2

2 5

3 1

1 3

2 3

3 0

1 3

2 2

3 3

etc

;

Is there a simple way to do this in data step (instead of using transpose or other procedures)?

Thanks for assistance with this.

Jason

Re: Change columns to observations

PGStats — Mon, 12 Nov 2018 19:24:56 GMT

Why not use the best tool for the job, i.e. proc transpose?

In a data step, put V1-V3 in an array and output each value in a do LOOP.

Re: Change columns to observations

novinosrin — Mon, 12 Nov 2018 19:25:34 GMT

data have;
input Obs v1 v2 v3;
cards;
1 2 3 3
2 5 3 2
3 1 0 3
;


;
run;
proc transpose data=have out=_h;
by _all_;
var v:;
run;
proc sort data=_h out=want(keep=col1);
by _name_;
run;

Re: Change columns to observations

Jason2020 — Mon, 12 Nov 2018 19:39:54 GMT

I know that I could use transpose. However, because the data set has other variables that I want be copied (not transposed) to the vertical output set I thought using the data step is less complicated.

Re: Change columns to observations

novinosrin — Mon, 12 Nov 2018 19:50:26 GMT

ok PG's suggested, loop and array should suffice

Re: Change columns to observations

Reeza — Mon, 12 Nov 2018 20:01:39 GMT

Yes there is, see this tutorial/write up on going from wide to long with a data step. Basically use the OUTPUT statement.
https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/

Re: Change columns to observations

Astounding — Mon, 12 Nov 2018 20:18:39 GMT

If you are really interested in changing just 3 variables, here's a very simple way:

data want;

set have;

v=v1;

output;

v=v2;

output;

v=v3;

output;

drop v1 v2 v3;

run;

If you actually have more than 3 variables, putting them into an array (as was suggested) is an excellent idea.

Re: Change columns to observations

Jason2020 — Mon, 12 Nov 2018 20:35:25 GMT

Thanks to all. All your replies were very useful. I appreciate it.

Jason

Re: Change columns to observations

Ksharp — Tue, 13 Nov 2018 12:55:47 GMT

Did I missed something ?

data have;
input Obs v1 v2 v3;
cards;
1 2 3 3
2 5 3 2
3 1 0 3
;

data want;
 set have(keep=obs v1 rename=(v1=v))
 have(keep=obs v2 rename=(v2=v))
 have(keep=obs v3 rename=(v3=v));
run;

Re: Change columns to observations

Astounding — Tue, 13 Nov 2018 15:28:47 GMT

Just a small point. In reality, the KEEP list is longer. You would either need to spell out a longer list, or switch to:

set have (drop=v2 v3 rename=(v1=v))

have (drop=v1 v3 rename=(v2=v))

have (drop=v1 v2 rename=(v3=v));

Re: Change columns to observations

Ksharp — Wed, 14 Nov 2018 12:19:47 GMT

Astounding,

You mean you save more code than me ?

Re: Change columns to observations

Astounding — Wed, 14 Nov 2018 13:24:53 GMT

Sort of. Buried in one of the messages is that OBS represents many variables, not just a single variable. The message didn't say how many, but if there were 30 variables that needed to be carried to the output, they all would need to appear in the KEEP list.

Re: Change columns to observations

Ksharp — Wed, 14 Nov 2018 13:44:42 GMT

OK. I know you .