1

I'm trying to edit this code to be dynamic as I'm going to schedule it to run. Normally I would input the date in the where statement as 'YYYY-MM-DD' and so to make it dynamic I changed it to DATE(). I'm not erroring out, but I'm also not pulling data. I just need help with format and my google searching isn't helping.

PROC SQL;
CONNECT TO Hadoop (server=disregard this top part);

CREATE TABLE raw_daily_fcast AS SELECT * FROM connection to Hadoop(
SELECT DISTINCT
    a.RUN_DATE,
    a.SCHEDSHIPDATE, 
    a.SOURCE,
    a.DEST ,
    a.ITEM,
    b.U_OPSTUDY, 
    a.QTY,
    c.case_pack_qty

FROM CSO.RECSHIP a
  LEFT JOIN CSO.UDT_ITEMPARAM b
    ON a.ITEM = b.ITEM
  LEFT JOIN SCM.DIM_PROD_PLN c
    ON a.ITEM = c.PLN_NBR

WHERE a.RUN_DATE = DATE()
    AND a.SOURCE IN ('88001', '88003', '88004', '88006', '88008', '88010', '88011', '88012', 
                     '88017', '88018', '88024', '88035', '88040', '88041', '88042', '88047')
);
DISCONNECT FROM Hadoop;
QUIT;
Lauren Neely
  • 31
  • 1
  • 5
  • 1
    Since you are following *Hadoop* dialect, check if `Date()` is an actual function. Is this Hadoop Hive? I am seeing [`current_date()`](https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions) is available. – Parfait Jul 09 '18 at 15:46
  • It is Hive. I just tried 'current_date()' and it also didn't pull any data. I know there is data since earlier I did '2018-07-09' and it pulled. But thank you anyway! – Lauren Neely Jul 09 '18 at 15:56
  • 1
    What is the data type of *RUN_DATE*? – Parfait Jul 09 '18 at 16:07
  • First thing i'd try: move current_date() to the SELECT and select everything (or, limit it hardcoded if need be). See what that returns. – Joe Jul 09 '18 at 16:33
  • '2018-07-09' is a string, date() will return a number. did you use '2018-07-09'd ? – DCR Jul 09 '18 at 16:38
  • RUN_DATE is date9 format – Lauren Neely Jul 09 '18 at 17:54

2 Answers2

0

change:

WHERE a.RUN_DATE = DATE() 

to:

WHERE a.RUN_DATE =  PUT(date(), YYMMDD10.) AS date 
DCR
  • 14,737
  • 12
  • 52
  • 115
0

When RUN_DATE is a string you can generate the current date string in-line on the SAS side

WHERE a.RUN_DATE = %str(%')%sysfunc(date(),yymmdd10.)%str(%')
   AND ...

or

WHERE a.RUN_DATE = %sysfunc(quote(%sysfunc(date(),yymmdd10.),%str(%')))
   AND ...

For the case of RUN_DATE being a string containing DATE9 formatted values, change the yymmdd10. to date9.

Richard
  • 25,390
  • 3
  • 25
  • 38