BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sayanapex06
Obsidian | Level 7

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 

?

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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."

View solution in original post

3 REPLIES 3
Astounding
PROC Star

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.

ballardw
Super User

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."

Reeza
Super User

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.

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
  • 3 replies
  • 821 views
  • 0 likes
  • 4 in conversation