topic Re: Compress rows as opposed to columns in SAS Procedures

Compress rows as opposed to columns

Thoney — Fri, 11 Jan 2013 13:45:19 GMT

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

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

Re: Compress rows as opposed to columns

Linlin — Fri, 11 Jan 2013 13:55:23 GMT

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;

Re: Compress rows as opposed to columns

Tom — Fri, 11 Jan 2013 13:58:00 GMT

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;

Re: Compress rows as opposed to columns

LinusH — Fri, 11 Jan 2013 14:04:45 GMT

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;

Re: Compress rows as opposed to columns

Linlin — Fri, 11 Jan 2013 14:08:35 GMT

Hi LinusH,

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

Re: Compress rows as opposed to columns

Tom — Fri, 11 Jan 2013 14:14:41 GMT

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=_:) max= ;

run;

Re: Compress rows as opposed to columns

Thoney — Fri, 11 Jan 2013 14:21:29 GMT

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.