Create a dataset

[radha009]

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

[Kurt_Bremser]

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.

[jonna28pr0]

Sir,

Could you please make me understand what happens if we don't use "if _n_ = 0 then set have;" statement? Thanks.

[Kurt_Bremser]

That is just there to line up the columns. Without that statement, the new variable would be leftmost. See it as cosmetics.