topic Create a dataset in SAS Programming

Create a dataset

radha009 — Wed, 23 Jan 2019 14:57:45 GMT

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

Re: Create a dataset

Kurt_Bremser — Wed, 23 Jan 2019 15:37:18 GMT

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.

Re: Create a dataset

jonna28pr0 — Wed, 23 Jan 2019 16:17:24 GMT

Sir,

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

Re: Create a dataset

Kurt_Bremser — Wed, 23 Jan 2019 17:23:03 GMT

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