Count of specific observations by ID and Date

[BlayLay]

I'm not sure of the best approach for this, so I'll provide a current table example and the desired output table. The first table is a series of records. The activity types are known, so I'm thinking we could use WHERE ActivityType = "App_New". But am open to any and all suggestions/help.

ID	Date	ActivityType
B123D	11/01/2020	APP_New
B123D	11/01/2020	APP_New
A932F	11/01/2020	APP_Edit
C456E	11/01/2020	APP_New
B123D	11/02/2020	APP_Edit
A932F	11/02/2020	APP_New
C456E	11/02/2020	APP_Edit
C456E	11/02/2020	APP_New

What I'm looking to accomplish, is to count the number of specific activity types by ID and by Date. So the "new" table would look something like this (see below), based off the above table.

Date	ID	Total_App_New	Total_App_Edit
11/01/2020	B123D	2	0
11/01/2020	A932F	0	1
11/01/2020	C456E	0	1
11/02/2020	B123D	0	1
11/02/2020	A932F	1	0
11/02/2020	C456E	1	1

[GertNissen]

data input;
  input ID $5. Date :mmddyy10. ActivityType $9.;
  format Date date.;
  datalines;
B123D 11/01/2020 APP_New
B123D 11/01/2020 APP_New
A932F 11/01/2020 APP_Edit
C456E 11/01/2020 APP_New
B123D 11/02/2020 APP_Edit
A932F 11/02/2020 APP_New
C456E 11/02/2020 APP_Edit
C456E 11/02/2020 APP_New
;
run;

proc sort data=input;
  by date id  ActivityType;
run;

data output;
  set input;
  by date id ;
  retain Total_App_New Total_App_Edit;

  if first.id then 
    do; 
      Total_App_New=0;
      Total_App_Edit=0;
    end;

  if ActivityType = 'APP_New' then Total_App_New+1;
  else if ActivityType = 'APP_Edit' then Total_App_Edit+1;

  if last.id then output;
run;

[novinosrin · Accepted solution]

Hi @BlayLay  You could resort to lazy way of doing it by utilizing Proc Freq SPARSE option and a simple transpose. Increasingly I am getting used to these methods-

data input;
  input ID $5. Date :mmddyy10. ActivityType $9.;
  format Date date9.;
  datalines;
B123D 11/01/2020 APP_New
B123D 11/01/2020 APP_New
A932F 11/01/2020 APP_Edit
C456E 11/01/2020 APP_New
B123D 11/02/2020 APP_Edit
A932F 11/02/2020 APP_New
C456E 11/02/2020 APP_Edit
C456E 11/02/2020 APP_New
;
run;

proc freq data=input;
 tables date*id*ActivityType/sparse noprint out=temp(drop=percent);
run;

proc transpose data=temp out=want(drop=_:) prefix=Total_;
 by date id;
 var count;
 id ActivityType;
run;

[GertNissen]

Great @novinosrin 👍

Good use of sparse - Was not aware of this one - very useful - thanks 

[PhilC]

Ditto

---

@GertNissen wrote:

Great @novinosrin 👍

Good use of sparse - Was not aware of this one - very useful - thanks 

---

[novinosrin]

Thank you @PhilC  , @GertNissen  for the note.  To be totally honest, I was taught these tricks by @PGStats  a while ago. Of course I tend to take notes of such geniuses , and this being yet another that PG advised me to quickly grasp three major equivalent options i.e-

Proc FREQ- SPARSE

Proc Summary/Means - COMPLETETYPES

Proc REPORT- COMPLETEROWS

Sir @PGStats  Whether you remember or not, Thank you sharing this that now these learning are nicely shared across the our platform. Cheers!

[BlayLay]

Thank you! This worked perfectly

[Ksharp]

data input;
  input ID $5. Date :mmddyy10. ActivityType $9.;
  format Date date9.;
  datalines;
B123D 11/01/2020 APP_New
B123D 11/01/2020 APP_New
A932F 11/01/2020 APP_Edit
C456E 11/01/2020 APP_New
B123D 11/02/2020 APP_Edit
A932F 11/02/2020 APP_New
C456E 11/02/2020 APP_Edit
C456E 11/02/2020 APP_New
;
run;
proc sql noprint;
select distinct cats("sum(ActivityType='",ActivityType,"') as total_",ActivityType)
 into : list separated by ','
  from input;
create table want as
select date,id,&list 
 from input
  group by date,id;
quit;