topic Re: SAS Query in SAS Programming

SAS Query

scb — Wed, 09 Jan 2019 09:56:09 GMT

I have the following data.

data test;
input period date9. case $;
format period date9.;
datalines;
11DEC2018 ABC1
12DEC2018 ABC2
13DEC2018 ABC1
13DEC2018 ABC2
01JAN2019 ABC1
02JAN2019 ABC2

;

Run;

May I know how to get the desired output - cases by month? Thanks.

Desired Output:

Period Cases

DEC2018 4

JAN2019 2

Re: SAS Query

PeterClemmensen — Wed, 09 Jan 2019 10:17:29 GMT

One way

data test;
input period date9. case $;
format period date9.;
datalines;
11DEC2018 ABC1
12DEC2018 ABC2
13DEC2018 ABC1
13DEC2018 ABC2
01JAN2019 ABC1
02JAN2019 ABC2
;
Run;

proc sql;
   create table want as
   select put(period, monyy7.) as period,
          count(period) as Cases
   from test
   group by calculated period;
quit;

Re: SAS Query

andreas_lds — Wed, 09 Jan 2019 10:20:50 GMT

Another approach:

proc summary data=work.test nway;
   class period;
   format period monyy7.;
   output out=work.want(drop=_type_ rename=(_freq_=Cases));
run;

Re: SAS Query

novinosrin — Wed, 09 Jan 2019 14:53:23 GMT

Good morning, Sure I was sleeping and too late to the party

data test;
input period date9. case $;
format period date9.;
datalines;
11DEC2018 ABC1
12DEC2018 ABC2
13DEC2018 ABC1
13DEC2018 ABC2
01JAN2019 ABC1
02JAN2019 ABC2
;
Run;

data _null_;
if _n_=1 then do;
   dcl hash H (ordered: "A") ;
   h.definekey  ("Period") ;
   h.definedata ("Period","cases") ;
   h.definedone () ;
end;
set test(rename=period=_period) end=lr;
Period=put(_period,monyy7.);
retain _p;
if period ne _p then do;cases=1;_p=period;end;
else cases+1;
h.replace();
if lr then h.output(dataset:'want');
run;