combining rows of data with conditions

[Kbug]

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? 

[novinosrin · Accepted solution]

data want;

set have;

by id;

if last.id;

run;

[ballardw]

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.