topic SAS cumulative count by unique ID and date in SAS Programming

SAS cumulative count by unique ID and date

Pradeepbanu — Thu, 04 Jun 2020 02:38:25 GMT

Hello All,

I have a dataset like below

Customer_ID	Vistited_Date
1234	7-Feb-20
4567	7-Feb-20
9870	7-Feb-20
1234	14-Feb-20
7654	14-Feb-20
3421	14-Feb-20

I am trying find the cumulative unique count of customers by date, assuming my output will be like below

Cust_count	Vistited_Date
3	7-Feb-20
2	14-Feb-20

7-Feb-2020 has 3 unique customers, whereas 14-Feb-2020 has only 2 hence customer 1234 has visited already.

Anyone knows how I could develop a data set in these conditions?

Sorry if my question is not clear enough, and I am available to give more details if necessary.

Thanks!

Re: SAS cumulative count by unique ID and date

ballardw — Thu, 04 Jun 2020 07:43:19 GMT

One classic approach is a double proc freq

proc freq data=have noprint;
   tables date*id/out=work.count1;
run;

proc freq data=work.count1 noprint;
   tables date / out=work.want;
run;

Misread the requirement.

Re: SAS cumulative count by unique ID and date

ChrisNZ — Thu, 04 Jun 2020 03:42:10 GMT

Perfect use for a hash table.

data WANT;
  if _N_=1 then do;
   dcl hash H(dataset:'HAVE(obs=0)');
   H.definekey('ID');
   H.definedone();
  end;
  set HAVE;
  by DATE;    
  if first.DATE then SUM=0;
  if H.check() then do;
    SUM+1;
    H.add();
  end;
  if last.DATE then output;
  keep DATE SUM;
run;

DATE	SUM
07FEB20	3
14FEB20	2

Re: SAS cumulative count by unique ID and date

ChrisNZ — Thu, 04 Jun 2020 03:43:27 GMT

@ballardw Your code does not count customers once only.

Re: SAS cumulative count by unique ID and date

Patrick — Thu, 04 Jun 2020 05:18:09 GMT

Not as neat as the hash approach but works as well.

data have;
  infile datalines dsd;
  input Customer_ID Visited_Date :anydtdte.;
  format Visited_Date date9.;
  datalines;
1234,7-Feb-20
4567,7-Feb-20
9870,7-Feb-20
1234,14-Feb-20
7654,14-Feb-20
3421,14-Feb-20
;

proc sort data=have out=inter;
  by Customer_ID Visited_Date;
run;

proc sort data=inter out=inter nodupkey;
  by Customer_ID;
run;

proc sql;
/*  create table want as*/
    select 
      count(customer_id) as cust_count, 
      Visited_Date
    from inter
    group by Visited_Date
    ;
quit;

Re: SAS cumulative count by unique ID and date

s_lassen — Thu, 04 Jun 2020 05:52:34 GMT

A single (although nested) SQL query should suffice:

proc sql;
  create table want as select Visited_date,count(distinct Customer_ID) as Cust_Count
    from(select * from have group by Customer_ID having Visited_date=min(Visited_date))
    group by Visited_Date;
quit;

Re: SAS cumulative count by unique ID and date

PeterClemmensen — Thu, 04 Jun 2020 07:36:01 GMT

data have;
input Customer_ID Vistited_Date :anydtdte12.;
format Vistited_Date date9.;
datalines;
1234 7-Feb-2020
4567 7-Feb-2020
9870 7-Feb-2020
1234 14-Feb-2020
7654 14-Feb-2020
3421 14-Feb-2020
;

data want (drop=Customer_ID);
    if _N_=1 then do;
        declare hash h ();
        h.definekey ('Customer_ID');
        h.definedone ();
    end;

    do until (last.Vistited_Date);
        set have;
        by Vistited_Date;
        if h.add() = 0 then Count = sum(Count, 1);
    end;
run;

Re: SAS cumulative count by unique ID and date

ed_sas_member — Thu, 04 Jun 2020 07:33:20 GMT

proc sort data=have out=have_nodup;
	by Visited_Date Customer_ID;
run;

proc sort data=have_nodup nodupkey;
	by Customer_ID;
run;

proc sql;
	select Visited_Date, count(Customer_ID) as Cust_count
	from have_nodup
	group by Visited_Date;
run;

Re: SAS cumulative count by unique ID and date

Pradeepbanu — Thu, 25 Jun 2020 13:56:21 GMT

Thanks for the quick solution!!