Using SAS global variable to be called in many sql procs

Question

I am very new to SAS and am struggling with how to define a global variable that can be used in many sql procs within my program

%LET seems to have a scope that is not global within my program..I'd like to declare a variable just once instead of having many %LET statements. My program has many SQL procs for different types of transaction monitoring. For example currently i have multiple proc sqls pulling data from the same table for various instruments, but i wanted to define a threshold in which i have a static value declared, like below:

%let x=10000;

Proc sql;
Select trans_amount, country from transactiontable
where trans_amount > &x
And ins_type in ('C')
Quit;

Proc sql;
Select trans_amount, country from transactiontable
where trans_amount > &x
And ins_type in ('D')
Quit;

Proc sql;
Select trans_amount, country from transactiontable
where trans_amount > &x
And ins_type in ('E')
Quit;

Here is the error message I am getting: WARNING: Apparent symbolic reference X not resolved. WARNING: Apparent symbolic reference X not resolved. 31 trans_amount> &X _ 22 ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, BTRIM, INPUT, PUT, SUBSTRING, USER.

Please note Trans_amount is a numeric value derived from a sql table (numeric 8 digits).

Can some one help me or guide me to any literature that might help in declaring a variable only once instead of having to declare on %LET before every PROC sql?

I am wondering if this is SAS's way of telling me that i have a type mismatch error. Also the reason why i have to seperate the ins_type is because there is a need for seperate reports for the different ins_types.

Unless you're using macros, there's no reason the &x. should lose its value (or you're assigning it a new value). You might need to explain more (show an example program where it occurs) to get useful advice. — Joe
If the &x is surrounded by single quotes (') then the value will not get resolved, in which case you would need to use double quotes ("). Showing the error message should also help. — Amir
Here is th error message I am getting: _ 22 ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, BTRIM, INPUT, PUT, SUBSTRING, USER. I am begining to wonder if this is something to do with my call for the variable. Also i need to correct myself, none of the procs work with the call for threshold_amount. — vbala2014
Please could you post the whole proc sql block where you're using &x? — user667489
Hi, I have made changes to the original post. Please bare with me as I am new to the forums/ coding world and especially SAS — vbala2014

kittymad kittymad · Accepted Answer · 2014-09-15T15:10:16

Once you declare a macro variable using the %let statement, this value is stored in the global table and you do not need to declare it before every proc sql (unless the value for the macro variable x is changed locally in a proc sql step).

When you run '%let x=10000;', the macro variable x is created and will exist during your entire SAS session.

From what I gather from your error, it appears that the global statement '%let x=10000;' was not run at all during the current SAS session.

Also, if you derive a variable within the current proc sql step, you might need to use 'where where calculated trans_amount > &x;' if you apply the subsetting in the same step. (- from what I can see in your amended question, this is not necessary in this case.)

In order for your code to run, you must declare the macro variable x once in your current SAS session.

I think this should solve the problem.

Using SAS global variable to be called in many sql procs

1 Answers