topic Count of specific observations by ID and Date in SAS Programming

Count of specific observations by ID and Date

BlayLay — Tue, 22 Dec 2020 14:19:18 GMT

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

Re: Count of specific observations by ID and Date

GertNissen — Tue, 22 Dec 2020 14:47:12 GMT

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;

Re: Count of specific observations by ID and Date

novinosrin — Tue, 22 Dec 2020 15:06:51 GMT

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;

Re: Count of specific observations by ID and Date

GertNissen — Tue, 22 Dec 2020 15:43:10 GMT

Great @novinosrin 👍

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

Re: Count of specific observations by ID and Date

PhilC — Tue, 22 Dec 2020 15:46:13 GMT

Ditto

@GertNissen wrote:

Great @novinosrin 👍

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

Re: Count of specific observations by ID and Date

BlayLay — Tue, 22 Dec 2020 16:21:47 GMT

Thank you! This worked perfectly

Re: Count of specific observations by ID and Date

Ksharp — Wed, 23 Dec 2020 12:23:20 GMT

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;

Re: Count of specific observations by ID and Date

novinosrin — Wed, 23 Dec 2020 18:39:19 GMT

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!