BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

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

11 REPLIES 11
Haikuo
Onyx | Level 15

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
Onyx | Level 15

Or even simpler:

data want ;

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

by column1;

run;

Haikuo

Linlin
Lapis Lazuli | Level 10

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
PROC Star

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
Onyx | Level 15

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
Lapis Lazuli | Level 10

Hi HaiLuo,

It seems there is an error in your hash method.

could you correct that?

Thanks

tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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
PROC Star

Not so different:

data want (drop=column3);

   set have;

   length admission $ 8;

   admission='noreadm';

   output;

   column2=column3;

   admission='yesreadm';

   output;

run;

Linlin
Lapis Lazuli | Level 10

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
Obsidian | Level 7

proc transpose data=have out=want;

by column1;

var column2 column3;

run;

Regards,

Oleg.

GeorgeSAS
Lapis Lazuli | Level 10

This is the simplest way

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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