Help using Base SAS procedures

make columns different

Accepted Solution Solved
Reply
Regular Contributor
Regular Contributor
Posts: 238
Accepted Solution

make columns different

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


Accepted Solutions
Solution
‎03-14-2012 11:35 AM
Super Contributor
Posts: 1,636

make columns different

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

View solution in original post


All Replies
Respected Advisor
Posts: 3,124

make columns different

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

Respected Advisor
Posts: 3,124

make columns different

Or even simpler:

data want ;

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

by column1;

run;

Haikuo

Super Contributor
Posts: 1,636

make columns different

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

Super User
Posts: 5,071

make columns different

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.

Respected Advisor
Posts: 3,124

Re: make columns different

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

Regular Contributor
Posts: 201

make columns different

Hi HaiLuo,

It seems there is an error in your hash method.

could you correct that?

Thanks

Regular Contributor
Regular Contributor
Posts: 238

make columns different

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

Super User
Posts: 5,071

make columns different

Not so different:

data want (drop=column3);

   set have;

   length admission $ 8;

   admission='noreadm';

   output;

   column2=column3;

   admission='yesreadm';

   output;

run;

Solution
‎03-14-2012 11:35 AM
Super Contributor
Posts: 1,636

make columns different

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

Regular Contributor
Posts: 151

make columns different

proc transpose data=have out=want;

by column1;

var column2 column3;

run;

Regards,

Oleg.

Regular Contributor
Posts: 201

make columns different

This is the simplest way

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 302 views
  • 1 like
  • 6 in conversation