make columns different

[tmm]

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

[Haikuo]

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

[Haikuo]

Or even simpler:

data want ;

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

by column1;

run;

Haikuo

[Linlin]

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

[Astounding]

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.

[Haikuo]

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

[GeorgeSAS]

Hi HaiLuo,

It seems there is an error in your hash method.

could you correct that?

Thanks

[tmm]

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

[Astounding]

Not so different:

data want (drop=column3);

   set have;

   length admission $ 8;

   admission='noreadm';

   output;

   column2=column3;

   admission='yesreadm';

   output;

run;

[Linlin · Accepted solution]

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

[Oleg_L]

proc transpose data=have out=want;

by column1;

var column2 column3;

run;

Regards,

Oleg.

[GeorgeSAS]

This is the simplest way