topic Re: Hadoop Passthrough in SAS Programming

Hadoop Passthrough

IgawaKei29 — Mon, 16 Mar 2020 00:39:01 GMT

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.

Re: Hadoop Passthrough

Patrick — Mon, 16 Mar 2020 01:06:58 GMT

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)

Re: Hadoop Passthrough

ChrisNZ — Mon, 16 Mar 2020 01:06:08 GMT

Like this?

%let today_hive = %sysfunc(date(),yymmddd10.);


where to_date(b.Element5) >= to_date(%nrbquote('&today_hive'))

Re: Hadoop Passthrough

IgawaKei29 — Mon, 16 Mar 2020 10:26:39 GMT

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!