Help using Base SAS procedures

declare public variable to be used as memname

Reply
Regular Contributor
Posts: 186

declare public variable to be used as memname

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.

PROC Star
Posts: 7,363

Re: declare public variable to be used as memname

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.

Regular Contributor
Posts: 186

Re: declare public variable to be used as memname

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;

Super Contributor
Posts: 1,636

Re: declare public variable to be used as memname

changing

proc sort data=sasuser.%data;

to

proc sort data=sasuser.&data;

PROC Star
Posts: 7,363

Re: declare public variable to be used as memname

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. 

Regular Contributor
Posts: 186

Re: declare public variable to be used as memname

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 !

PROC Star
Posts: 7,363

Re: declare public variable to be used as memname

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

Regular Contributor
Posts: 186

Re: declare public variable to be used as memname

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

Ask a Question
Discussion stats
  • 7 replies
  • 496 views
  • 4 likes
  • 3 in conversation