1

I'm making a report data for SAS VA. Example from data:

Date - Customers

01Mar2019 - 4

02Mar2019 - 5

03Mar2019 - 2

05Mar2019 - 9

So I got 0 customers on 04Mar2019. That day does not show in my report data as 0 because it's not in the original data.

Is there a handy way to get all dates of (all) year(s) from SAS EG and then just left join the work to it?

Output should be:

01Mar2019 - 4

02Mar2019 - 5

03Mar2019 - 2

04Mar2019 - 0

05Mar2019 - 9

Any tips?

Thanks!

RR91
  • 11
  • 2
  • Please can you show the specific piece of code that's not working or Please share your effort – Lalji Dhameliya Apr 09 '19 at 13:46
  • How are you getting the counts now? Show the code you are using. If you are are starting with the summary table then how do you know if there are missing leading or trailing dates? – Tom Apr 09 '19 at 18:22
  • Code is something like: select open_dt, count(distinct customer_id)as count group by open_dt. – RR91 Apr 10 '19 at 07:46

1 Answers1

0

Not sure what you are actually doing but you could just generate a dataset with one observations per date and merge them together.

data have ;
  input date customers;
  informat date date. ;
  format date date9.;
cards;
01Mar2019 4
02Mar2019 5
03Mar2019 2
05Mar2019 9
;

proc sql ;
  create table all_dates as 
  select min(date) as mindate, max(date) as maxdate
  from have
  ;
quit;

data all_dates;
  set all_dates;
  do date=mindate to maxdate;
    customers=0;
    output;
  end;
  format date date9.;
  keep date customers;
run;

data want;
  merge all_dates have;
  by date;
run;

Results:

Obs         date    customers

 1     01MAR2019        4
 2     02MAR2019        5
 3     03MAR2019        2
 4     04MAR2019        0
 5     05MAR2019        9
Tom
  • 47,574
  • 2
  • 16
  • 29