topic Re: Selecting DB2 Data based on Date into SAS using proc SQL in SAS Programming

Selecting DB2 Data based on Date into SAS using proc SQL

PMORSING — Thu, 02 Jun 2016 09:43:33 GMT

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 🙂

Re: Selecting DB2 Data based on Date into SAS using proc SQL

Doc_Duke — Thu, 02 Jun 2016 12:42:10 GMT

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.

Re: Selecting DB2 Data based on Date into SAS using proc SQL

PMORSING — Fri, 03 Jun 2016 11:09:29 GMT

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