topic Global Variable not referenced in SAS Programming

Global Variable not referenced

sayanapex06 — Tue, 13 Feb 2018 14:47:13 GMT

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

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

Re: Global Variable not referenced

Astounding — Tue, 13 Feb 2018 14:56:24 GMT

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.

Re: Global Variable not referenced

ballardw — Tue, 13 Feb 2018 15:09:27 GMT

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

Re: Global Variable not referenced

Reeza — Tue, 13 Feb 2018 15:50:18 GMT

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.