BookmarkSubscribeRSS Feed
nicnad
Fluorite | Level 6

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.

7 REPLIES 7
art297
Opal | Level 21

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.

nicnad
Fluorite | Level 6

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;

Linlin
Lapis Lazuli | Level 10

changing

proc sort data=sasuser.%data;

to

proc sort data=sasuser.&data;

art297
Opal | Level 21

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. 

nicnad
Fluorite | Level 6

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 !

art297
Opal | Level 21

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

nicnad
Fluorite | Level 6

Works great now thank you very much for your help and time.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

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
  • 7 replies
  • 1806 views
  • 4 likes
  • 3 in conversation