So I understand how to set up automatic dates in SAS:
/* Normal Date (today) */
%let Today_SAS = %sysfunc(date(),Date9.);
%LET Run_Date = %SYSFUNC(INPUTN(&Today_SAS., Date9.));
/*This will give the date 3 months back */
%LET Three_SAS = %SYSFUNC(INTNX(MONTH, &Run_Date., -3., B), Date9.);
/* This will give the date 6 months back */
%LET Six_SAS = %SYSFUNC(INTNX(MONTH, &Run_Date., -6., B), Date9.);
/* This will give the date 9 months back */
%LET Nine_SAS = %SYSFUNC(INTNX(MONTH, &Run_Date., -9., B), Date9.);
/* This will give the date 12 months back */
%LET Twelve_SAS = %SYSFUNC(INTNX(MONTH, &Run_Date., -12., B), Date9.);
My Question is how do I pass this macro into a Hadoop HIVE passthrough?
Here is a crude example below of the code I am using to pass through to Hadoop. I am hoping to automate the date process, but I am at a loss on this one.
Proc SQL noerrorstop;
Connect to HADOOP (server='xxx' port=xxx);
Execute (set mapreduce.job.queuename=sas.xxx) by HADOOP;
Execute (set parquet.compression=SNAPPY) by HADOOP;
Execute (drop table if exists test.test) by HADOOP;
Execute (create table test.test stored as parquet as
select distinct
a.Element1,
a.Element2,
a.Element3,
a.Element4,
b.Element5
from Test.Test2 as a
LEFT JOIN Test.Test3 b on
a.ElementJoin=b.ElementJoin
where to_date(b.Element5) >= ('2018-01-01')
) by hadoop;
quit;
Thanks for any suggestions or helpful material to reference.
From the Hive reference found here.
cast(string as date) |
If the string is in the form 'YYYY-MM-DD', then a date value corresponding to that year/month/day is returned. If the string value does not match this formate, then NULL is returned. |
Or here a whole list of options if using to_date()
And now you can use your macro variables. You just need to enclose the values into single quotes when using in the Hive SQL. Here how that can be done:
%let Today_SAS = %sysfunc(date(),Date9.);
%LET Run_Date = %SYSFUNC(INPUTN(&Today_SAS., Date9.));
/*This will give the date 3 months back */
%LET Three_SAS = %SYSFUNC(INTNX(MONTH, &Run_Date., -3., B), Date9.);
/*Method 1:*/
%put Method 1: %unquote(%str(%'&Run_Date%'));
/*Method 2 (If running SAS 9.4 or greater):*/
%put Method 2: %tslit(&Run_Date);
Solution above inspired by here.
For your where clause:
where to_date(b.Element5) >= cast(%tslit(&Run_Date) as date)
From the Hive reference found here.
cast(string as date) |
If the string is in the form 'YYYY-MM-DD', then a date value corresponding to that year/month/day is returned. If the string value does not match this formate, then NULL is returned. |
Or here a whole list of options if using to_date()
And now you can use your macro variables. You just need to enclose the values into single quotes when using in the Hive SQL. Here how that can be done:
%let Today_SAS = %sysfunc(date(),Date9.);
%LET Run_Date = %SYSFUNC(INPUTN(&Today_SAS., Date9.));
/*This will give the date 3 months back */
%LET Three_SAS = %SYSFUNC(INTNX(MONTH, &Run_Date., -3., B), Date9.);
/*Method 1:*/
%put Method 1: %unquote(%str(%'&Run_Date%'));
/*Method 2 (If running SAS 9.4 or greater):*/
%put Method 2: %tslit(&Run_Date);
Solution above inspired by here.
For your where clause:
where to_date(b.Element5) >= cast(%tslit(&Run_Date) as date)
Like this?
%let today_hive = %sysfunc(date(),yymmddd10.);
where to_date(b.Element5) >= to_date(%nrbquote('&today_hive'))
Thank you both! @ChrisNZ and @Patrick both those solutions seem to work for what I need. I wasn't sure if the passthru would accept SAS macro logic however I learned that the SAS system will first build the packet that it will be sending over to Hadoop/HIVE, which means that SAS will interpret the macro date first and make sure that my variable is added prior to it sending over the packet.
So I learned something from this. Appreciate you both again.
Thanks!
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.