topic Re: Calculate number of daily occurences for a particular month in SAS Programming

Calculate number of daily occurences for a particular month

Mona32256 — Tue, 06 Oct 2020 17:57:02 GMT

I have dataset that looks like this

Client

Lowes 01Sept2020

Home Dept 01Sept2020

Lowes 01Sep2020

Lowes 02Sep2020

looking for

Lowes 01Sep2020 count is 2

Home Depot 01Sep2020 count is 1

I want to calculate daily occurrence for Lowes and Home Dept and find median or Average for month of Sep

I was able to get all dates for September but cannot figure out how to do the count? I have dataset query but need help with looping through each date to get daily records

here is loop for getting dates 01Sep2020 02Sep2020.....

proc sql;
%macro date_loop(start,end);
%let start=%sysfunc(inputn(&start,anydtdte9.));
%let end=%sysfunc(inputn(&end,anydtdte9.));
%let dif=%sysfunc(intck(day,&start,&end));
%do i=0 %to &dif;
%let date=%sysfunc(intnx(day,&start,&i,b),date9.);
%put &date;
%end;
%mend date_loop;
%date_loop(01sep2020,30sep2020)
quit;

Thanks you in advance

Mona

Re: Calculate number of daily occurences for a particular month

jimbarbour — Tue, 06 Oct 2020 19:02:18 GMT

I may be missing something, but is a Macro necessary?

Consider the following code. The results are below the code.

I am able to get a total by client by date with this code with no Macro coding. Don't get me wrong. I like Macro coding. But I am not seeing that it is strictly necessary here. Generally, Data steps are a good way to work with data.

If we add more data, then we can start getting things like averages.

DATA		Client_Data;
	INFILE	DATALINES	;
	FORMAT	Client	$25.;
	FORMAT	Date	MMDDYYS10.;

	Input	Client	&
			Date	:	ANYDTDTE10.
			;
DATALINES;
Lowes    01Sept2020
Home Dept  01Sept2020
Lowes   01Sep2020
Lowes  02Sep2020
;
RUN;

PROC	SORT	DATA=Client_Data;
	BY	Client	Date;
RUN;

DATA	Client_Summary;
	SET	Client_Data;
		BY	Client	Date;

	IF	LAST.Date	THEN
		DO;
			Client_Date_Total	+	1;
			OUTPUT;
			Client_Date_Total	=	0;
			DELETE;
		END;
	ELSE
		DO;
			Client_Date_Total	+	1;
			DELETE;
		END;
RUN;

Results:

Jim

Re: Calculate number of daily occurences for a particular month

Mona32256 — Tue, 06 Oct 2020 19:12:51 GMT

Thank you very much for your response.

Being new to SAS,

1) how shouldI reference dataset that I got from query in below code

create table Auth_data as
select * from Client where updatedate is not null and updatedate2 is not null....

2) I used macro to set limit on dates. Guess I can do that by putting it in where clause. something like updatedate is between 01sep2020 and 30sep2020. will that work?

Re: Calculate number of daily occurences for a particular month

ChanceTGardener — Tue, 06 Oct 2020 19:23:29 GMT

If I understand, you want the distinct count of each Client|Date.

data client_data;
infile	datalines;
format	client	$25.;
format	date	mmddyys10.;
input	client & date :	anydtdte10.;
datalines;
lowes    01sept2020
home dept  01sept2020
lowes   01sep2020
lowes  02sep2020
;
run;

proc sql;
 create table work.want as
 select distinct client
 ,		 date
 ,		 count(*) as count
 from client_data
 group by client, date
 order by client, date;
quit;

Re: Calculate number of daily occurences for a particular month

jimbarbour — Tue, 06 Oct 2020 19:27:07 GMT

1) You should refer to the table you created in the SQL as:

WORK.Auth_data

2) Yes, dates are a very appropriate thing to include in a WHERE clause. You could code the dates as macro values if you like.

%LET Start = '01sep2020'd;
%LET Stop = '30sep2020'd;

PROC SQL
    create table Auth_data as
          select * from Client 
              where updatedate is not null 
              AND updatedate2 is not null
              AND updatedate between &Start  and &Stop
              ;

I'm assuming that UpdateDate is a numeric SAS date and not datetime or character.

Jim