Here is my code :
%LET T_DATA = 'NBKUP' ;
%LET INDTE = '02/11/2017' ;
%LET INCLS = 'YES' ;
%PUT 'SAYANX :' &T_DATA &INDTE &INCLS ;
libname dwmdb oracle user = 'idsdba01' password = 'imag1ne' path = 'GQDWD_DEV'; /* DEV */
proc sql;
CREATE TABLE BACKUP1 as
(select * from dwmdb.t_compute_tx_tbl
where TYPE_OF_DATA = "&T_DATA"
and INPUT_DATE = "&INDTE"
and INCLUDE_STATUS = "&INCLS" ) ;
quit;
proc sql;
CREATE TABLE BACKUP2 as
( select * from dwmdb.t_compute_tx_tbl
where TYPE_OF_DATA = 'NBKUP'
and INPUT_DATE = '02/11/2017'
and INCLUDE_STATUS = 'YES' ) ;
quit;
SASLOG :
NOTE: SAS initialization used:
real time 0.02 seconds
cpu time 0.01 seconds
1
2 %LET T_DATA = 'NBKUP' ;
3 %LET INDTE = '02/11/2017' ;
4 %LET INCLS = 'YES' ;
5
6 %PUT 'SAYANX :' &T_DATA &INDTE &INCLS ;
'SAYANX :' 'NBKUP' '02/11/2017' 'YES'
7
8
9
10
11 libname dwmdb oracle user = 'idsdba01' password = XXXXXXXXX path = 'GQDWD_DEV';
NOTE: Libref DWMDB was successfully assigned as follows:
Engine: ORACLE
Physical Name: GQDWD_DEV
11 ! /* DEV */
12
13
14
15 proc sql;
16 CREATE TABLE BACKUP1 as
17
18 (select * from dwmdb.t_compute_tx_tbl
19 where TYPE_OF_DATA = "&T_DATA"
2 The SAS System 09:45 Tuesday, February 13, 2018
20 and INPUT_DATE = "&INDTE"
21 and INCLUDE_STATUS = "&INCLS" ) ;
NOTE: Table WORK.BACKUP1 created, with 0 rows and 35 columns.
22 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.98 seconds
cpu time 0.05 seconds
23
24
25
26 proc sql;
27 CREATE TABLE BACKUP2 as
28
29 ( select * from dwmdb.t_compute_tx_tbl
30 where TYPE_OF_DATA = 'NBKUP'
31 and INPUT_DATE = '02/11/2017'
32 and INCLUDE_STATUS = 'YES' ) ;
NOTE: Table WORK.BACKUP2 created, with 42077 rows and 35 columns.
33 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 6.92 seconds
cpu time 0.81 seconds
34
NOTE: SAS Institute Inc., SAS Campus Drive, Cary, NC USA 27513-2414
NOTE: The SAS System used:
real time 8.30 seconds
cpu time 0.92 seconds
I want to know why the first query is not fetching any records, and the second query being same(hard-coded) fetches records
?
With
LET T_DATA = 'NBKUP' ;
use
where TYPE_OF_DATA = &T_DATA
otherwise
where TYPE_OF_DATA = "&T_DATA"
generates code that looks like
where TYPE_OF_DATA = " 'NBKUP' " so unless your data set variable contains the single quotes you don't want that.
Probably better is not to include the single quotes in the macro variable value.
LET T_DATA = NBKUP ;
and
where TYPE_OF_DATA = "&T_DATA."
Your %LET statements are assigning the wrong values.
There should not be any quotes to the right of the equal sign in these %LET statements.
With
LET T_DATA = 'NBKUP' ;
use
where TYPE_OF_DATA = &T_DATA
otherwise
where TYPE_OF_DATA = "&T_DATA"
generates code that looks like
where TYPE_OF_DATA = " 'NBKUP' " so unless your data set variable contains the single quotes you don't want that.
Probably better is not to include the single quotes in the macro variable value.
LET T_DATA = NBKUP ;
and
where TYPE_OF_DATA = "&T_DATA."
Your syntax needs to be valid, so lets see what it resolves to:
proc sql;
CREATE TABLE BACKUP1 as
(select * from dwmdb.t_compute_tx_tbl
where TYPE_OF_DATA = "&T_DATA"
and INPUT_DATE = "&INDTE"
and INCLUDE_STATUS = "&INCLS" ) ;
quit;
BECOMES
proc sql;
CREATE TABLE BACKUP1 as
(select * from dwmdb.t_compute_tx_tbl
where TYPE_OF_DATA = "'NBKUP'"
and INPUT_DATE = "'02/11/2017' "
and INCLUDE_STATUS = "'YES'" ) ;
quit;
Note the mistakes:
1. single and double quotes - that won't resolve the way you want it to
2. Date as character - if your date is a SAS date this is specified incorrectly.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.