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 🙂
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.