include missing metrics

[DME790]

Hi All,

I have a dataset made up of four metrics - AAA, BBB, CCC and DDD.

Not all metrics are returned for each day but I need to show these as 'Blanks'

The below code returns the right information as per the data  

data test;
input CD $ event_dt :yymmdd. Count;
format event_dt yymmdd10. ;
label event_dt='Event Date';
datalines;
AAA 2018-07-06 10
BBB 2018-07-06 12
DDD 2018-07-06 8
AAA 2018-07-06 16
DDD 2018-07-06 13
BBB 2018-07-07 19
CCC 2018-07-07 4
BBB 2018-07-07 8
;
Run;

proc Sort data=work.test;
	By event_dt cd;
run;

Proc Summary data=test sum;
 By event_dt CD;
 Var Count;
Output out=work.Final (Drop=_freq_ _Type_) sum=;
run;

event_dt	CD	Count
2018-07-06	AAA	26
2018-07-06	BBB	12
2018-07-06	DDD	21
2018-07-07	BBB	27
2018-07-07	CCC	4

However I would like this to be returned - How can I add the missing metric as a blank into the data?

event_dt	CD	Count
6/07/2018	AAA	26
6/07/2018	BBB	12
6/07/2018	CCC
6/07/2018	DDD	21
7/07/2018	AAA
7/07/2018	BBB	27
7/07/2018	CCC	4
7/07/2018	DDD

Any help appreciated.

Cheers

Dean

[novinosrin]

data test;
input CD $ event_dt :yymmdd. Count;
format event_dt yymmdd10. ;
label event_dt='Event Date';
datalines;
AAA 2018-07-06 10
BBB 2018-07-06 12
DDD 2018-07-06 8
AAA 2018-07-06 16
DDD 2018-07-06 13
BBB 2018-07-07 19
CCC 2018-07-07 4
BBB 2018-07-07 8
;
Run;

proc freq data=test noprint;
table event_dt*cd/out=temp(drop=percent count) sparse ;
run;

proc sql;
create table want as
select a.*, sum(count) as count 
from temp a left join test b
on a.event_dt=b.event_dt and a.cd=b.cd
group by a.event_dt, a.cd;
quit;

[PGStats]

Use completetypes option and CLASS instead of BY:

data test;
input CD $ event_dt :yymmdd. Count;
format event_dt yymmdd10. ;
label event_dt='Event Date';
datalines;
AAA 2018-07-06 10
BBB 2018-07-06 12
DDD 2018-07-06 8
AAA 2018-07-06 16
DDD 2018-07-06 13
BBB 2018-07-07 19
CCC 2018-07-07 4
BBB 2018-07-07 8
;

Proc Summary data=test sum nway completetypes;
 class event_dt CD;
 Var Count;
Output out=Final (Drop=_freq_ _Type_) sum=;
run;

proc print noobs; run;

                               event_dt    CD     Count

                             2018-07-06    AAA      26
                             2018-07-06    BBB      12
                             2018-07-06    CCC       .
                             2018-07-06    DDD      21
                             2018-07-07    AAA       .
                             2018-07-07    BBB      27
                             2018-07-07    CCC       4
                             2018-07-07    DDD       .

[novinosrin]

Brilliant!!!

[DME790]

Thanks @PGStats and @novinosrin ,

Both work a treat - How would I get the same if DDD had had no response to date but still include it

data test;
input CD $ event_dt :yymmdd. Count;
format event_dt yymmdd10. ;
label event_dt='Event Date';
datalines;
AAA 2018-07-06 10
BBB 2018-07-06 12
AAA 2018-07-06 16
BBB 2018-07-07 19
CCC 2018-07-07 4
BBB 2018-07-07 8
;

Proc Summary data=test sum nway completetypes;
 class event_dt CD;
 Var Count;
Output out=Final (Drop=_freq_ _Type_) sum=;
run;

proc print noobs; run;

Thanks heaps

Dean

[novinosrin]

Well if that's not in the input dataset, I'm afraid you may have to append that with the result.

Prolly PG might have a better idea. 

[PGStats · Accepted solution]

Yes this can be done with a preloaded format, as shown here

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a000353486.htm

[FredrikE]

Awesome!!!

Such an easy way to create a "full" table, thanks!

//Fredrik