Hi,
Is there a way in sas to declare a variable once and use it in multiple procedures?
The thing is I am using a table named "blue_cars_table" in multiple procedures and instead of writing blue_cars_table I would like to write a variable, so if a user want to change the code quickly, he just have to change the table name once.
Something like (in VBA)
Public A as string
A = "blue_cars_table"
In the following code, I would like the variable "data" to represent "blue_cars_table" so that the following code would work :
*** Something like Public data as string
data = "blue_cars_table"
proc sort data=sasuser.data;
by legal_entity;
run;
proc sql NOPRINT;
select quote(CATS(name)) into :var separated by ',' from dictionary.columns where libname='SASUSER' AND MEMNAME=data;
quit;
data _null_;
dcl hash h(ordered: 'a');
h.definekey('_n_');
h.definedata (&VAR);
h.definedone();
do _n_=1 by 1 until (last.legal_entity);
set SASUSER.data;
by legal_entity notsorted;
h.add();
end;
h.output (dataset:'work.'||compress(legal_entity,,'kda'));
run;
Hope my explanation is clear. Thank you for your help and time.
That is what macro variables are for. You are already creating and using one, namely &VAR.
You could do what you want by creating one called, say, &data.
e.g., %let data=blue_cars_table;
Then, you could use it in your code by replacing data with &data.
Thank you for the reply.
I tried this code :
%let data=blue_cars_table;
proc sort data=sasuser.%data;
by legal_entity;
run;
and I get the following error message :
Some of your options or statements may not be supported with the Activex or Java series of devices. Graph defaults for these
drivers may be different from other SAS/GRAPH device drivers. For further information, please contact Technical Support.
9 OPTIONS DEV=ACTIVEX;
10 FILENAME EGHTML TEMP;
NOTE: Writing HTML(EGHTML) Body file: EGHTML
11 ODS HTML(ID=EGHTML) FILE=EGHTML ENCODING='utf-8' STYLE=EGDefault
11 ! STYLESHEET=(URL="file:///C:/Program%20Files/SAS/Shared%20Files/BIClientStyles/EGDefault.css")
11 ! ATTRIBUTES=("CODEBASE"="http://www2.sas.com/codebase/graph/v91/sasgraph.exe") NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation
11 ! ;
12
13 %gaccessible;
14 %let data=blue_cars_table;
15
16 proc sort data=sasuser.%data;
WARNING: Apparent invocation of macro DATA not resolved.
16 proc sort data=sasuser.%data;
_
22
ERROR 22-322: Syntax error, expecting one of the following: ;, (, ASCII, BUFFNO, DANISH, DATA, DATECOPY, DETAILS, DIAG, DUPOUT,
EBCDIC, EQUALS, FINNISH, FORCE, IN, ISA, L, LEAVE, LIST, MESSAGE, MSG, NATIONAL, NODUP, NODUPKEY, NODUPKEYS,
NODUPLICATE, NODUPLICATES, NODUPREC, NODUPRECS, NODUPS, NOEQUALS, NORWEGIAN, NOTHREADS, OSA, OUT, OVERWRITE,
PAGESIZE, PSIZE, REVERSE, SIZE, SORTSEQ, SORTSIZE, SORTWKNO, SWEDISH, T, TAGSORT, TECH, TECHNIQUE, TESTHSI, THREADS,
WKNO, WORKNO.
16 proc sort data=sasuser.%data;
____
202
ERROR 202-322: The option or parameter is not recognized and will be ignored.
WARNING: Apparent invocation of macro DATA not resolved.
ERROR: File WORK.SASUSER.DATA does not exist.
17 by legal_entity;
18 run;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
19
20 %LET _CLIENTTASKLABEL=;
21 %LET _EGTASKLABEL=;
22 %LET _CLIENTPROJECTNAME=;
23 %LET _SASPROGRAMFILE=;
24
25 ;*';*";*/;quit;run;
26 ODS _ALL_ CLOSE;
2 The SAS System 10:47 Thursday, August 2, 2012
27
28
29 QUIT; RUN;
changing
proc sort data=sasuser.%data;
to
proc sort data=sasuser.&data;
As Linlin pointed out, you refer to macro variables by preceding them with an ampersand (i.e., a &). You use a % symbol to call a SAS macro.
Thank you for your help.
The macro variable works great with proc sort, but I get an error when I use memname=&data
This is the code I run :
%let data=blue_cars_table;
proc sql NOPRINT;
select quote(CATS(name)) into :var separated by ',' from dictionary.columns where libname='SASUSER' AND MEMNAME=&data;
quit;
And this is the error I get...
NOTE: Some of your options or statements may not be supported with the Activex or Java series of devices. Graph defaults for these
drivers may be different from other SAS/GRAPH device drivers. For further information, please contact Technical Support.
9 OPTIONS DEV=ACTIVEX;
10 FILENAME EGHTML TEMP;
NOTE: Writing HTML(EGHTML) Body file: EGHTML
11 ODS HTML(ID=EGHTML) FILE=EGHTML ENCODING='utf-8' STYLE=EGDefault
11 ! STYLESHEET=(URL="file:///C:/Program%20Files/SAS/Shared%20Files/BIClientStyles/EGDefault.css")
11 ! ATTRIBUTES=("CODEBASE"="http://www2.sas.com/codebase/graph/v91/sasgraph.exe") NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation
11 ! ;
12
13 %gaccessible;
14 proc sql NOPRINT;
15
16 select quote(CATS(name)) into :var separated by ',' from dictionary.columns where libname='SASUSER' AND MEMNAME=&data;
ERROR: The following columns were not found in the contributing tables: BLUE_CARS_TABLE.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
17
18 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
19
20 %LET _CLIENTTASKLABEL=;
21 %LET _EGTASKLABEL=;
22 %LET _CLIENTPROJECTNAME=;
23 %LET _SASPROGRAMFILE=;
24
25 ;*';*";*/;quit;run;
26 ODS _ALL_ CLOSE;
27
28
29 QUIT; RUN;
Can you please help me fix this?
Thank you !
Two problems: one, in this use &data. has to be enclosed with double quotation marks. Two, in this case you will also have to use the upcase function for it to work correctly.
e.g., memname=upcase("&data.")
Works great now thank you very much for your help and time.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.