topic Re: Is this code counting frequency in 12 months? Can someone explain how it works? in SAS Enterprise Guide

Is this code counting frequency in 12 months? Can someone explain how it works?

Sofiya — Mon, 12 Dec 2022 14:58:14 GMT

PROC SQL;
CREATE TABLE WORK.Retailer_Frequency_Insval AS
SELECT t1.retailer,
(COUNT(t1.retailer)) AS Retailer_Frequency
FROM SASDATA.insval_history t1
WHERE t1.date >= (intnx('year', "&DT."d, -1))
GROUP BY t1.retailer
ORDER BY t1.retailer;
QUIT;

Re: Is this code counting frequency in 12 months? Can someone explain how it works?

Tom — Mon, 12 Dec 2022 15:15:35 GMT

No.

It is counting the number of observations from the beginning of last year.

So if the macro variable DT has a string like, 12DEC2022, that the DATE informat can interpret as a date in the year 2022 it counts from the beginning of 2021.

2031  data _null_;
2032    dt='12DEC2022';
2033    date = input(dt,date11.);
2034    index=intnx('year',date,-1);
2035    put (date index) (=date9.);
2036  run;

date=12DEC2022 index=01JAN2021

Re: Is this code counting frequency in 12 months? Can someone explain how it works?

Kurt_Bremser — Mon, 12 Dec 2022 15:16:40 GMT

It depends on the contents of macro variable DT. The code will summarize from 1 year before DT up to the max date in the dataset.

Re: Is this code counting frequency in 12 months? Can someone explain how it works?

PeterClemmensen — Mon, 12 Dec 2022 15:18:15 GMT

@Tom , wouldn't it be the number of non-missing values of t1.retailer since the beginning of last year?

Re: Is this code counting frequency in 12 months? Can someone explain how it works?

Sofiya — Mon, 12 Dec 2022 15:21:03 GMT

So, it is going to count number of observation for last 12 months even we will be in 2023, right? Is this correct way to count number of observation for the reports?

Re: Is this code counting frequency in 12 months? Can someone explain how it works?

Kurt_Bremser — Mon, 12 Dec 2022 16:00:54 GMT

Since retailer is also the group variable, its count will be equal to the number of observations, except for the one group where retailer is missing (if such exists at all).

Re: Is this code counting frequency in 12 months? Can someone explain how it works?

Kurt_Bremser — Mon, 12 Dec 2022 16:03:24 GMT

Not for the last 12 months. If run with today's date, it would count all observations for 24 months (January 2021 to December 2022).

Re: Is this code counting frequency in 12 months? Can someone explain how it works?

Sofiya — Mon, 12 Dec 2022 16:08:13 GMT

Ohh I see. That was my concern. What can I do to run only last 12months from today?

For example, today is 12/22/22, I believe it should be counting the records from 12/13/2021 to 12/12/2022 which would be 12 months of data.

Re: Is this code counting frequency in 12 months? Can someone explain how it works?

Tom — Mon, 12 Dec 2022 16:37:30 GMT

@Sofiya wrote:

So, it is going to count number of observation for last 12 months even we will be in 2023, right? Is this correct way to count number of observation for the reports?

It depends.

1) What is the value of the DT macro variable? Where is that set? What value does it have?

2) What is the range of dates in the dataset?

If you want to count 12 months then say so explicitly in the code.

Something like:

where datevar between intnx('year',"&dt"d,-1,'s') and "&dt"d

Re: Is this code counting frequency in 12 months? Can someone explain how it works?

Sofiya — Mon, 12 Dec 2022 18:40:57 GMT

Where can i find micro variable location? I went to Tools -> SAS micro variable viewers -> searched there, but I could not find the date variable. Is there is another way to find?

Re: Is this code counting frequency in 12 months? Can someone explain how it works?

Kurt_Bremser — Mon, 12 Dec 2022 19:21:23 GMT

Then you do this:

proc sql;
create table work.Retailer_Frequency_Insval as
  select
    t1.retailer,
    (count(t1.retailer)) as Retailer_Frequency
  from sasdata.insval_history t1
  where t1.date > intnx('year',today(),-1,'s')
  group by t1.retailer
;
quit;

ORDER BY is not necessary, as GROUP BY will result in a sorted order already.

Re: Is this code counting frequency in 12 months? Can someone explain how it works?

Sofiya — Mon, 12 Dec 2022 21:18:45 GMT

Tom, I can not see the result. It created the table but there is no value in it.

Re: Is this code counting frequency in 12 months? Can someone explain how it works?

Sofiya — Mon, 12 Dec 2022 21:26:09 GMT

@Kurt_Bremser, I ran the code but I can not see the result. It created the table but there is no value in it.

Re: Is this code counting frequency in 12 months? Can someone explain how it works?

Tom — Mon, 12 Dec 2022 21:30:20 GMT

Make sure that the variable you are filtering on (T1.DATE) is actually a DATE variable.

Make sure that the time period you want is actually there. Try doing a quick check:

proc freq data=sasdata.insval_history ;
  tables date ;
  format date year4. ;
run;

Re: Is this code counting frequency in 12 months? Can someone explain how it works?

Sofiya — Mon, 12 Dec 2022 21:36:04 GMT

I can't find the date variable. Where I should go to check it?

Re: Is this code counting frequency in 12 months? Can someone explain how it works?

Kurt_Bremser — Mon, 12 Dec 2022 21:36:55 GMT

Please post the complete (all code, all messages) log of the PROC SQL by copy/pasting it into a window opened with this button: