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

Hello,
I am using SAS 9.4 and what I am trying to accomplish is combining multiple rows of data, I use proc summary to do this however I want the rows with 0's to show, below is an example part of my data:

 

Set: have

 

ID          count    

ABC          1                  

ABC          2                  
ABC          3                 
ABC          4                  

DEF          0                  
GHI           1                   
JKL           1                  
JKL            2                   

MNO          1                  
MNO          2                  
MNO          3                   

PQR           1                   

 

Set: want

 

ID          count      

ABC          4                  

DEF          0                  
GHI           1                  
JKL            2                   

MNO          3                  

PQR           1                   

 

What I am trying to achieve is combining multiple rows of the same data together as a summary. So each row will have one unique ID. I want DEF, and any other rows that I have in my data that has 0 to be present and I am currently unable to have DEF be present with my proc summary and it just  seems to get deleted. How do I make sure DEF shows up on the final file? 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

 

data want;

set have;

by id;

if last.id;

run;

 

View solution in original post

2 REPLIES 2
novinosrin
Tourmaline | Level 20

 

data want;

set have;

by id;

if last.id;

run;

 

ballardw
Super User

It would likely help to show the Proc Summary code you are using currently.

 

From the example you show it would appear that you want the max value from your "have" and if that example is actually what you have the max statistic would return the shown want.

Example:

data have;
   input id $ count;
datalines;
ABC          1                  
ABC          2                  
ABC          3                 
ABC          4                  
DEF          0                  
GHI           1                   
JKL           1                  
JKL            2                   
MNO          1                  
MNO          2                  
MNO          3                   
PQR           1 
;

proc summary data=have nway;
   class id;
   var count;
   output out=want (drop= _type_ _freq_) max= ;
run;


proc print data=want noobs ;
run;
 
Result:
id     count

ABC      4
DEF      0
GHI      1
JKL      2
MNO      3
PQR      1


So apparently you may be missing details of either your actual starting data or the description.

 

You may want to provide a small example data set as a data step as above, if your actual data doesn't actually look like that, with desired output.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 864 views
  • 2 likes
  • 3 in conversation