topic Re: combining rows of data with conditions in SAS Programming

combining rows of data with conditions

Kbug — Thu, 17 Oct 2019 21:45:25 GMT

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?

Re: combining rows of data with conditions

novinosrin — Thu, 17 Oct 2019 21:50:47 GMT

data want;

set have;

by id;

if last.id;

run;

Re: combining rows of data with conditions

ballardw — Thu, 17 Oct 2019 21:59:58 GMT

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.