topic Re: declare public variable to be used as memname in SAS Procedures

declare public variable to be used as memname

nicnad — Wed, 01 Aug 2012 21:13:39 GMT

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.

Re: declare public variable to be used as memname

art297 — Wed, 01 Aug 2012 21:51:12 GMT

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.

Re: declare public variable to be used as memname

nicnad — Thu, 02 Aug 2012 14:42:05 GMT

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;

Re: declare public variable to be used as memname

Linlin — Thu, 02 Aug 2012 15:05:57 GMT

changing

proc sort data=sasuser.%data;

proc sort data=sasuser.&data;

Re: declare public variable to be used as memname

art297 — Thu, 02 Aug 2012 15:18:49 GMT

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.

Re: declare public variable to be used as memname

nicnad — Thu, 02 Aug 2012 16:01:09 GMT

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 !

Re: declare public variable to be used as memname

art297 — Thu, 02 Aug 2012 16:07:18 GMT

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

Re: declare public variable to be used as memname

nicnad — Thu, 02 Aug 2012 17:03:32 GMT

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