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
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.
@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.
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
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?
How about actually store date information as SAS date values instead of year/ month (and possibly day of month)?
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...
DATE between
/*two full months ago, start*/
intnx('month', today(), -3, 'b')
and
/*last month last day*/
intnx('month', today(), -1, 'e');
@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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.