SAS Query

[scb]

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

[PeterClemmensen · Accepted solution]

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;

[andreas_lds]

Another approach:

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

[novinosrin]

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;