i want to create a table similar to below, for an id multiple and unique summary descriptions and the total count. Is it possible?
id | summary | summary cnt | total_ct |
123 | Oranges | 1 | 2 |
Grapes | 1 | ||
345 | blueberry | 1 | 3 |
Banana | 2 | ||
Apple | 1 |
To exactly create your output, you need proc report with an intermediate step:
data have;
input id $ summary :$10. summary_cnt;
cards;
123 Oranges 1
123 Grapes 1
345 blueberry 1
345 Banana 2
345 Apple 1
;
run;
data want;
if _n_ = 0 then set have;
total_ct = 0;
do until (last.id);
set have;
by id;
total_ct + 1;
end;
do until (last.id);
set have;
by id;
output;
total_ct = .;
end;
run;
options missing = ' ';
proc report data=want;
column id summary summary_cnt total_ct;
define id / group;
define summary / display;
define summary_cnt / display;
define total_ct / display;
run;
Result:
summary_c id summary nt total_ct 123 Oranges 1 2 Grapes 1 345 blueberry 1 3 Banana 2 Apple 1
Edit: added options missing.
Sir,
Could you please make me understand what happens if we don't use "if _n_ = 0 then set have;" statement? Thanks.
That is just there to line up the columns. Without that statement, the new variable would be leftmost. See it as cosmetics.
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.