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
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;
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;
Hi LinusH,
I don't understand neither:smileysilly:! I learned from Tom's posts.
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;
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.