SAS macros

[astha8882000]

Hello,

I'm trying to understand few concepts about the macro variables through this piece of code:

%macro check;

     %global test;

     %do;

     proc sql noprint;

     CONNECT TO SQLSVR (datasrc=teradata user=%sysget(USER) password="&passwrd.");

     select * into : test

from connection to SQLSVR

      (SELECT MAX(height) from profile_table)

disconnect from SQLSVR;

                quit;

           %end;

           %sysrput test= &test;

%mend check;

%check;

option nosource;

%put test= &test.;

option source;

Next to each line, I'm adding what I think that statement does, requesting your help to confirm if my understanding is correct:

%macro check;

     %global test;//creates a global macro variable

     %do;

     proc sql noprint;

     CONNECT TO SQLSVR (datasrc=teradata user=%sysget(USER) password="&passwrd.");

     select * into : test //does a refer to the global macro variable created above? Or is test some other variable that gets created remotely on the server location? what is the purpose of this statement?

from connection to SQLSVR

      (SELECT MAX(height) from profile_table)

disconnect from SQLSVR;

                quit;

           %end;

           %sysrput test = &test //value of remote test (which is in the select statement) gets transferred into global macro variable test defined at the very top??

%mend check;

%check;

option nosource;

%put test = &test.;//which test is being displayed here? the global macro variable or the one in the select statement or?

option source;

Also, why is it written test. in some places and test in other places(with and without the period)?

Thank you for your help!

[Tom]

So these lines mark the start and the end of the definition and then an execution of a macro named CHECK.

%macro check;
...
%mend check;
%check;

This line is making sure that TEST is defined in the global symbol table.

     %global test;

And these lines create a block of statements. Why they are there I have no idea as they are not serving any purpose in the program.

%do;
...
%end;

This block of statements will run a query against an external database and store the result into the macro variable TEST.  (assuming that the  semi-colon that I added to end the SELECT statement was actually in the macro definition)

proc sql noprint;
CONNECT TO SQLSVR (datasrc=teradata user=%sysget(USER) password="&passwrd.");
select * into : test from connection to SQLSVR
   (SELECT MAX(height) from profile_table)
;
disconnect from SQLSVR;
quit;

Then this line will push the value of TEST back down to the SAS session that remote submitted this whole program and create in that SAS session another macro variable that is also named TEST.

%sysrput test= &test;

Then finally these lines are writing the text TEST = and the value of TEST to the log, without echoing the %PUT statement itself to the log.

option nosource;
%put test= &test.;
option source;

When you are referencing a macro variable and you want to immediately follow the name of a macro variable you are referencing with a character that SAS could consider part of the macro variable name you must add the period so that SAS knows where the name ends and the other text starts.  But when the name of the macro variable is followed by a space or a semi-colon or other character that cannot be part of the macro variable name then the period is optional.

[hashman]

select * into : test //does a refer to the global macro variable created above? 

Yep. It's a global macro variable on the remote host. The %global statement makes it available outside the macro.

what is the purpose of this statement?

A proc SQL request to store the calculated value of MAX(height) from profile_table in the macro variable TEST. 

%sysrput test = &test //value of remote test (which is in the select statement) gets transferred into global macro variable test defined at the very top??

Nope. it's a request to copy the value of the macro variable TEST on the remote host into macro variable TEST on your local host, so that you could use it in the local session after the SAS/Connect session is over. 

Also, why is it written test. in some places and test in other places(with and without the period)?

A period after a macro variable reference tells SAS to dereference only the part between the ampersand and the period. After the variable is resolved, the period is ignored.Thus, if the value of macro variable TEST is 100, the reference &TEST._SMTH will result in 100_SMTH. Without the period, i.e. &TEST_SMTH, SAS would think that this is a reference to a macro variable whose name is TEST_SMTH. If it exists, you may get a wrong result; if it doesn't, SAS will tell you that it cannot be resolved.

Note that only the first period immediately following the macro variable name is treated in this manner; any subsequent period is just text. Thus, if a macro variable LIB contains value SASHELP and a macro variable DSN contains value CLASS and you wanted to get SASHELP.CLASS, you'd code &LIB..&CLASS since the first period after &LIB would be ignored after &LIB is resolved but the second will stay.

Some people have the habit of coding a period after a macro variable reference no matter whether's is needed (as in the example above) or not (as in your code sample) as some sort of "safety measure". Personally, I agree with Ian Whitlock that coding something that serves no purpose is wrong practice and code a period after a macro variable name only if what follows it can be interpreted as that name's continuation.

Paul D.