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

[Sofiya]

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;

[Tom]

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

[PeterClemmensen]

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

[Kurt_Bremser]

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).

[Sofiya]

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?

[Kurt_Bremser]

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).

[Sofiya]

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.

[Kurt_Bremser]

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.

[Sofiya]

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

[Sofiya]

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

[Tom]

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;

[Sofiya]

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

[Kurt_Bremser]

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

[Tom]

---

@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

[Sofiya]

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?