topic make columns different in SAS Procedures

make columns different

tmm — Wed, 14 Mar 2012 12:55:42 GMT

I have a table of 87k rows and it looks like this

column1 column2 column3

1111 14 2

1112 17 4

1113 25 9

I want to in SAS make it like this

column1 column2

1111 14

1111 2

1112 17

1112 4

1113 25

1113 9

make columns different

Haikuo — Wed, 14 Mar 2012 13:01:15 GMT

data have;

input column1 column2 column3;

cards;

1111 14 2

1112 17 4

1113 25 9

;

data want (drop= column2 column3 rename=c=column2);

set have;

array col column2 column3;

do _n_=1 to dim(col);

c=col(_n_);

output;

end;

run;

proc print;run;

Haikuo

make columns different

Haikuo — Wed, 14 Mar 2012 13:04:40 GMT

Or even simpler:

data want ;

set have (drop=column3) have(drop=column2 rename=column3=column2);

by column1;

run;

Haikuo

make columns different

Linlin — Wed, 14 Mar 2012 13:06:24 GMT

data have;

input column1 column2 column3;

cards;

1111 14 2

1112 17 4

1113 25 9

;

data want (drop=column3);

set have;

array _col(2) column2 column3;

do _n_=1 to 2;

column2=_col(_n_);

output;

end;

run;

proc print;run;

obs column1 column2

1 1111 14

2 1111 2

3 1112 17

4 1112 4

5 1113 25

6 1113 9

make columns different

Astounding — Wed, 14 Mar 2012 13:41:55 GMT

A simple way:

data want (drop=column3);

set have;

output;

column2 = column3;

output;

run;

The array versions are more scalable if you have lots of variables that require the same processing. But I'm not so sure that you need that.

Good luck.

Re: make columns different

Haikuo — Wed, 14 Mar 2012 14:05:51 GMT

This is gona look silly, but FWIW, here it goes;

Proc SQL:

proc sql;

create table want (drop=column3) as

select a.* from have a

outer union corr

select b.* from have (drop=column2 rename=column3=column2) b

order by column1;

quit;

Hash():

data want (drop=column3);

if _n_=1 then do;

set have (obs=1);

dcl hash h(dataset: 'have (drop=column2 rename=column3=column2)');

h.definekey('column1');

h.definedata('column2');

h.definedone();

end;

set have ;

output;

_n_=h.find();

if _n_=0 then output;

run;

Regards,

Haikuo

make columns different

tmm — Wed, 14 Mar 2012 15:09:07 GMT

Ok, now with astoundings fix which works great and simple. I put fake column names for sake of privacy. Not sure if I can really put the correct column names but do not see any privacy issues so going to put actual names of columns

provtin noreadm yesreadm

I used astoundings query and now it is

provtin noreadm

1111 14

1111 2

1112 145

1112 9

etc. It does it correctly but I want to add a column into the mix that is maybe titled Admission so my final output looks like this

provtin admission count

1111 noreadm 14

1111 yesreadm 2

1112 noreadm 145

1112 yesreadm 9

etc

make columns different

Oleg_L — Wed, 14 Mar 2012 15:09:17 GMT

proc transpose data=have out=want;

by column1;

var column2 column3;

run;

Regards,

Oleg.

make columns different

Astounding — Wed, 14 Mar 2012 15:14:46 GMT

Not so different:

data want (drop=column3);

set have;

length admission $ 8;

admission='noreadm';

output;

column2=column3;

admission='yesreadm';

output;

run;

make columns different

Linlin — Wed, 14 Mar 2012 15:35:38 GMT

data have;

input provtin noreadm yesreadm;

cards;

1111 14 2

1112 17 4

1113 25 9

;

data want (drop=noreadm yesreadm);

set have;

array _col(2) noreadm yesreadm;

do _n_=1 to 2;

admission=vname(_col(_n_));

count=_col(_n_);

output;

end;

run;

proc print;run;

Obs provtin admission count

1 1111 noreadm 14

2 1111 yesreadm 2

3 1112 noreadm 17

4 1112 yesreadm 4

5 1113 noreadm 25

6 1113 yesreadm 9

make columns different

GeorgeSAS — Mon, 19 Mar 2012 01:05:42 GMT

This is the simplest way

make columns different

GeorgeSAS — Mon, 19 Mar 2012 01:58:32 GMT

Hi HaiLuo,

It seems there is an error in your hash method.

could you correct that?

Thanks