Selecting DB2 Data based on Date into SAS using proc SQL

[PMORSING]

Hi

I'm having a little challenge on reading some  rows from an zos DB2 using SAS-PROCSQL .

How do I code my SAS  to  select on date  from the DB2 database :

In this  particular case the date is variabel  KMG_CREATE_DATE , se below  desc..  

DB2 variabel/column  :  KMG_CREATE_DATE        10 DATE          4

eg  in below code  I  would want to    select on   "db2 date"  being e.g   20160101   and/or   'similar  '01JAN2016' 

but as it it wrtitten here  it does NOT  work   .. !!!

%LET USSID=GES0;     
%LET CREATOR=KRYPTO;

LIBNAME MYLIB DB2 SSID=&USSID. SCHEMA=&CREATOR.;

PROC SQL;                     
 CREATE TABLE WORK.UKDS2 AS   
 SELECT KMG_KNO,              
        KMG_KNO_VER,          
        KMG_KEY_LABEL_1,      
        KMG_KEY_LABEL_2,      
        KMG_KEY_LABEL_3,      
        KMG_KEY_LABEL_4,      
        KMG_CREATE_DATE,      
        KMG_ACTIVE_DATE,      
        KMG_UPDATE_DATE,      
        KMG_LAST_USER_ID   

 
 FROM  MYLIB.TSSUKDS2      

 WHERE

 (     KMG_KNO       LIKE '0554-E%'           OR
        KMG_KNO       LIKE '0393-P%'          OR
        KMG_KNO       LIKE '0582-E%')       AND 

        KMG_CREATE_DATE >=   db2 date 

 ;                                    
 QUIT;                                   

RUN;  

DATA  UKDS2;

  SET   UKDS2;                 
RUN
 ;                                      
                          

thnak you   in advance   🙂

[Doc_Duke]

Since you are not using a passthru query, the db2 dates are converted into SAS dates (or, perhaps, datetime variables) and you can manipulate them in SAS just like other SAS dates.

I would suggest that you start by doing a simple SAS listing of a few rows to see what the data look like to SAS and modify your query from there.

[PMORSING]

Hi Doc_Duke

tks for your prompt answer ...  I  think i know what you mean,   basically access   the KMG_CREATE_Date  i a later  raw SAS step 

which might  also be a good idea.   But  what i did not write  perhaps was that I  was thinking about being able  to passthrough a data to DB2 in the proc sql  which can be used to   limit  the amount of rows extracted from DB2 .. 

e.g  build a macro variable  with a call symput ,a nd then use that  macro variable in the proc sql   to passthrough to DB2 

to limit the amount of returned rows. 

e.g 

first build tha   variable  "db2_date"  : 

DATA _NULL;                                                  
 CALL SYMPUT('INT_DATE',"'"||PUT(SASDATE,YYMMDDD10.)||"'");
RUN;                                                        

 then in the proc sql

something like 
 

proc sql

where  ...
  kmg_create_data =>  &INT_DATE      

but i cannot get it to work     😞 

best regards