BookmarkSubscribeRSS Feed
PMORSING
Calcite | Level 5

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   🙂

 

2 REPLIES 2
Doc_Duke
Rhodochrosite | Level 12

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
Calcite | Level 5

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

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1034 views
  • 0 likes
  • 2 in conversation