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

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 915 views
  • 0 likes
  • 4 in conversation