DATA Step, Macro, Functions and more

Global Variable not referenced

Accepted Solution Solved
Reply
Contributor
Posts: 54
Accepted Solution

Global Variable not referenced

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 

?


Accepted Solutions
Highlighted
Solution
‎02-13-2018 12:10 PM
Super User
Posts: 13,283

Re: Global Variable not referenced

Posted in reply to sayanapex06

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


All Replies
Super User
Posts: 6,622

Re: Global Variable not referenced

Posted in reply to sayanapex06

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.

Highlighted
Solution
‎02-13-2018 12:10 PM
Super User
Posts: 13,283

Re: Global Variable not referenced

Posted in reply to sayanapex06

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

Super User
Posts: 23,224

Re: Global Variable not referenced

Posted in reply to sayanapex06

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 95 views
  • 0 likes
  • 4 in conversation