Help using Base SAS procedures

Compress rows as opposed to columns

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Compress rows as opposed to columns

Hi Guys,

One of my colleagues just asked me a SAS question and I couldn't find a solution for it online. Imagine there are 3 observations in a table like the following;

Acct no        data 1     data 2    data 3

12345678      data

12345678                   data

12345678                                  data

and he wants it to look as the following;

Acct no        data 1     data 2    data 3

12345678      data       data       data

Now I'm still learning SAS, but I know that if you need to compress a column, you use the COMPRESS command, however how do we go about compressing row-wise instead?

Kind Regards,

Tom


Accepted Solutions
Solution
‎01-11-2013 08:58 AM
Super User
Super User
Posts: 7,060

Re: Compress rows as opposed to columns

This type of thing could be done using the UPDATE statement.  Read the manual to make sure you understand what it does, but basically it will only change the value of a variable when the new record has a non-missing value.  So the result will be the last non-missing value for each column.  Which appears to be what you want.  The UPDATE statement requires two datasets.  But the first dataset could be empty.

data want ;

  update have(obs=0) have;

  by account_no ;

run;

View solution in original post


All Replies
Super Contributor
Posts: 1,636

Re: Compress rows as opposed to columns

data dd;
input id var$;
cards;
123456 data
;
data have;
set dd(rename=var=data1) dd(rename=var=data2)dd(rename=var=data3);

run;

/**************************/
data want;
update have(obs=0)have;
by id;
proc print;run;

Super User
Posts: 5,432

Re: Compress rows as opposed to columns

Very neat , I wouldn't come up with that!

Actually, I don't really understand how that works..:smileyconfused:

I would thought of some SQL, like:

proc sql;

     select id, max(data1) as data1, max(data2) as data2, max(data3) as data3

          from have

          group by id

     ;

quit;

Data never sleeps
Super Contributor
Posts: 1,636

Re: Compress rows as opposed to columns

Hi LinusH,

I don't understand neither:smileysilly:! I learned from Tom's posts.

Super User
Super User
Posts: 7,060

Re: Compress rows as opposed to columns

If you only want the MAX (or other summary function) instead the value last entered then you can use PROC SUMMARY.  When there is only one non-missing value per account number the methods are the same.  With PROC SUMMARY the data will not need to be sorted because you can use the CLASS statement instead of a BY statement.

proc summary data=have nway missing ;

  class account_no;

  var date1 date2 date3 ;

  output out=want (drop=_Smiley Happy max= ;

run;

Solution
‎01-11-2013 08:58 AM
Super User
Super User
Posts: 7,060

Re: Compress rows as opposed to columns

This type of thing could be done using the UPDATE statement.  Read the manual to make sure you understand what it does, but basically it will only change the value of a variable when the new record has a non-missing value.  So the result will be the last non-missing value for each column.  Which appears to be what you want.  The UPDATE statement requires two datasets.  But the first dataset could be empty.

data want ;

  update have(obs=0) have;

  by account_no ;

run;

Occasional Contributor
Posts: 8

Re: Compress rows as opposed to columns

Hi Tom,

Fantastic little section of code that did the job in 4 very short lines! Thanks a lot, and Linlin, ever helpful responses as usual as well, I'm sure your code works too but I tried Tom's to start with because it was a little shorter and I'm lazy (hehe)

Again thanks a lot people, the help is appreciated beyond belief! Starting to become really glad I joined this forum!

Kind Regards,

Tom.

🔒 This topic is solved and locked.

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

Discussion stats
  • 6 replies
  • 207 views
  • 6 likes
  • 4 in conversation