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?
data want;
set have;
by id;
if last.id;
run;
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.
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 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.