topic Re: Select Data Based on Recent Two Months in SAS Programming

Select Data Based on Recent Two Months

BCNAV — Tue, 28 Aug 2018 13:50:29 GMT

I have the following in a macro loop that works as I want it:

PROC SQL;
    	CREATE TABLE EGTASK.LATESTA_OAG_&aerodrome._ALL AS 
    	SELECT ("&aerodrome") AS Airport, 
               t1.Year, 
               t1.Month, 
               t1.&aerodrome._OAG_PERCENT AS OAG_PERCENT_ALL
        FROM EGTASK.TAM_TWRS_OAG_PERCENT_F_ALL t1
    	WHERE t1.Year = &curryear AND t1.Month = &currmonth;
    QUIT;

Please note the WHERE step.

I'd like to modify this so that the final output always gives that last two months of data. For example, if it is run today, August 28 2018, the output will give the last two FULL months, which would be June and July of 2018.

This would be tricky for January and February, which would reference months in the previous year. Could intnx solve this?

Thanks

Re: Select Data Based on Recent Two Months

RW9 — Tue, 28 Aug 2018 14:08:29 GMT

Yes, intnx would be better:

proc sql;
  create table egtask.latesta_oag_&aerodrome._all as 
  select "&aerodrome" as airport, 
         year, 
         month, 
         &aerodrome._oag_percent as oag_percent_all
  from   egtask.tam_twrs_oag_percent_f_all
  where intnx('month',date,-2) < datepart(today());
quit;

Note how I don't shout my code at you. Now intnx uses dates, as having year an month only will be trickier to handle (as you say going over the year for instance). I would avoid having year and month parts in both the data and in macro variables. Create an actual date variable which will allow you check to do these calculations easier, even if you just default day=1 in.

Re: Select Data Based on Recent Two Months

PeterClemmensen — Tue, 28 Aug 2018 14:10:57 GMT

Add

where intnx("month",mdy(month, 1, Year),-2, "b") < datepart(today());

as a where clause. I assume you do not have an actual date variable since you use Year and Month in your code?

Re: Select Data Based on Recent Two Months

Reeza — Tue, 28 Aug 2018 14:40:40 GMT

@RW9 There's several best practices that recommend that designated words (SELECT, FROM, INNER JOIN etc.) are all caps and all variables and table names are lower case to differentiate between the two. Especially in SQL.

Re: Select Data Based on Recent Two Months

ballardw — Tue, 28 Aug 2018 14:57:39 GMT

How about actually store date information as SAS date values instead of year/ month (and possibly day of month)?

Re: Select Data Based on Recent Two Months

BCNAV — Tue, 28 Aug 2018 15:15:33 GMT

The reason why there is no day value is because these are aggregated monthly counts at air traffic control towers. Only monthly data is required, hence no date field is provided to me of individual flights. I can make one via:

mdy(t1.Month,1,t1.Year) with a format of MMDDYY8.

Now, when I use: intnx('month',t1.date,-2) < datepart(today()); Nothing is returned

The data now looks like:

Airport	Date	        Year	Month	Count
CYXX	1/1/2018	2018	1	5,091
CYXX	2/1/2018	2018	2	6,148
CYXX	3/1/2018	2018	3	11,069
CYXX	4/1/2018	2018	4	11,359
CYXX	5/1/2018	2018	5	14,552
CYXX	6/1/2018	2018	6	11,333
CYXX	7/1/2018	2018	7	7,842

I only want the last two months ever sent out. As can been seen above, that is June and July.

If I use intnx('month',t1.date,-2) < today(); then everything is returned.

I suspect this is close...

Re: Select Data Based on Recent Two Months

Reeza — Tue, 28 Aug 2018 15:49:47 GMT

DATE between  
                  /*two full months ago, start*/ 
                  intnx('month', today(), -3, 'b') 
           and
                  /*last month last day*/
                  intnx('month', today(), -1, 'e');

Re: Select Data Based on Recent Two Months

RW9 — Tue, 28 Aug 2018 15:50:26 GMT

Yep, SAS pushes out a lot of the bad practices as well, missing dots at the end of macro variables, '";run;quit; type nonsense. All promoting bad coding. Maybe I should just reply all my posts in uppercase with no punctuation or spaces or line feeds in future

Re: Select Data Based on Recent Two Months

ballardw — Tue, 28 Aug 2018 16:19:01 GMT

@BCNAV wrote:

The reason why there is no day value is because these are aggregated monthly counts at air traffic control towers. Only monthly data is required, hence no date field is provided to me of individual flights. I can make one via:

mdy(t1.Month,1,t1.Year) with a format of MMDDYY8.

Now, when I use: intnx('month',t1.date,-2) < datepart(today()); Nothing is returned

The data now looks like:
Airport	Date	        Year	Month	Count
CYXX	1/1/2018	2018	1	5,091
CYXX	2/1/2018	2018	2	6,148
CYXX	3/1/2018	2018	3	11,069
CYXX	4/1/2018	2018	4	11,359
CYXX	5/1/2018	2018	5	14,552
CYXX	6/1/2018	2018	6	11,333
CYXX	7/1/2018	2018	7	7,842
I only want the last two months ever sent out. As can been seen above, that is June and July.

If I use intnx('month',t1.date,-2) < today(); then everything is returned.

I suspect this is close...

A date value also simplifies addressing the "two previous months" when you are in February and you needed two year/month combinations. or when management comes back and says "what about 3 (or 4 or 5 or …) months previously, or comparing first quarter of this year with first quarter of last year …

TODAY function returns a date value. See:

data _null_;
  x=today();
  put x date9.;
run;

When you use datepart(today()) you have told SAS to treat Today as datetime value and is way off:

data _null_;
  x=datepart(today());
  put x date9.;
run;