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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

6 REPLIES 6
Linlin
Lapis Lazuli | Level 10

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;

LinusH
Tourmaline | Level 20

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

Hi LinusH,

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

Tom
Super User Tom
Super User

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;

Tom
Super User Tom
Super User

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;

Thoney
Calcite | Level 5

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.

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
  • 6 replies
  • 1403 views
  • 6 likes
  • 4 in conversation