BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DME790
Pyrite | Level 9

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_dtCDCount
2018-07-06AAA26
2018-07-06BBB12
2018-07-06DDD21
2018-07-07BBB27
2018-07-07CCC4

 

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

event_dtCDCount
6/07/2018AAA26
6/07/2018BBB12
6/07/2018CCC 
6/07/2018DDD21
7/07/2018AAA 
7/07/2018BBB27
7/07/2018CCC4
7/07/2018DDD 

 

Any help appreciated.

 

Cheers

 

Dean

 

1 ACCEPTED SOLUTION

Accepted Solutions
7 REPLIES 7
novinosrin
Tourmaline | Level 20
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
Opal | Level 21

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       .

 

PG
novinosrin
Tourmaline | Level 20

Brilliant!!!

DME790
Pyrite | Level 9

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
Tourmaline | Level 20

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
Opal | Level 21

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

PG
FredrikE
Rhodochrosite | Level 12

Awesome!!!

 

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

 

//Fredrik

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1057 views
  • 6 likes
  • 4 in conversation