Macro Variables Visibility

[deleted_user]

Hi guys.

Can, please, somebody say me why I need redefine the variable in this code?
proc sql noprint;
select count(*)
into :numrows
from perm.schedule
where year(begin_date)=2002;
%let numrows=&numrows; * why should I do this?
%put There are &numrows courses in 2002;
select course_code, location, begin_date format=mmddyy10.
into :crsid1-:crsid&numrows,
:place1-:place&numrows,
:date1-:date&numrows
from perm.schedule
where year(begin_date) = 2002
order by begin_date;
quit;

Also I need to do something like this:
do i=1 to numrows;
data _null_;
file input;
put ".......";
run;

proc metadata;
...
run;
end;
But it's not allowed. What the solution can be used for this?

[deleted_user]

1. split proc sql;
proc sql noprint;
select count(*)
into :numrows
from perm.schedule
where year(begin_date)=2002;
quit;

%put There are &numrows courses in 2002;

proc sql;
select course_code, location, begin_date format=mmddyy10.
into :crsid1-:crsid&numrows,
:place1-:place&numrows,
:date1-:date&numrows
from perm.schedule
where year(begin_date) = 2002
order by begin_date;
quit;

2. Use macro to create LOOP.

M.

[deleted_user]

1) You don't need to do the %let statement. Why would you think you would need to do that?

2) I think either you or someone is not understanding what SAS is, how it works, etc.

First, it looks like you are thinking in terms of traditional programming and programming languages, which is going to get you all messed up with SAS.

SAS is a data centric language, not a process and control language.

The base structure is the data step. Proc SQL is a good tool for extracting data from a database and putting it into a dataset.

Why are you loading up a bunch of macro variables? I expect you are making more work for yourself than you need to. SAS does a lot for you, automatically, to minimize the coding/programming effort, but the mindset is different.

Macro's are not meant for processing. Macro's are meant for inserting statements, and modularizing SAS statements.

What you want to do is not really clear from what you have provided. Tell us in english first, then we can give you some guidance.

[Cynthia_sas]

Hi:
Actually there are 2 purposes for these statements:
[pre]
%let numrows=&numrows; * why should I do this?
%put There are &numrows courses in 2002;
[/pre]

1) the %let statement strips all the leading and trailing spaces from &NUMROWS because there might be some instances where you do NOT want leading and trailing spaces
and
2) the %put just shows that the select into worked

I routinely use both statements as a best practice, because if I ever use &NUMROWS in a title or or footnote, I do not want leading or trailing spaces.

If you put these statements ABOVE the %let, you'll see what I mean in the LOG:

[pre]
%put xxxxx &numrows xxxxx;

%let numrows = &numrows;
%put yyyyy &numrows yyyyy;
[/pre]

Let's say you had run your code on SASHELP.SHOWS, which has 395 obs, the results of the %PUT would be:

[pre]
xxxxx 395 xxxxx
yyyyy 395 yyyyy
[/pre]


It's not a big deal to leave off the %let, it is something that we teach in the Macro class and you will see in a lot of examples.

cynthia

[deleted_user]

perfect explanation 🙂

[deleted_user]

Also, would you be so kind to help me again? After these 2 sql queries:

proc sql noprint;
select count(*)
into :numrows
from perm.schedule
where year(begin_date)=2002;
%let numrows=&numrows; * why should I do this?

select course_code, location, begin_date format=mmddyy10.
into :crsid1-:crsid&numrows,
:place1-:place&numrows,
:date1-:date&numrows
from perm.schedule
where year(begin_date) = 2002
order by begin_date;
quit;

I want to do something like this:
data _null_;
do i=1 to &numrows;
call symput('i', i);
%put &&crsid&i;
end;
run;

But it does not work like I want. The idea is to output all the elements of dimension. I still do not understand completely how does it work... please, help me with this line %put &&crsid&i;. What do I do wrong?

Thanks

[deleted_user]

You are not understanding SAS, and especially macros.

If you want to list a dataset, simply use:
[pre]
proc print data=perm.schedule(where=(year(begin_date)=2002));
run;
quit;
[/pre]

Macro's are blocks of text that are compiled and then used to inject SAS source code when the macro is executed, before the regular SAS source text is compiled.

There are many reasons why
[pre]
data _null_;
do i=1 to &numrows;
call symput('i', i);
%put &&crsid&i;
end;
run;
[/pre]
does not work.

1) symput publishes the macro variable "i" during datastep execution. The later reference to "&i" was already compiled, and was most probably nothing.

2) %put &&crsid&i was executed after the scan of the datastep and executed then, before the compilation of the data step and thus before the execute of the data step.

3) %put does not get compiled into the data step, so your actual loop, when executed, if numrows = 100, is
[pre]
do i=1 to 100;
end;
[/pre]

I would highly recommend that you spend a lot more time reading the SAS documentation, thoroughly. The SAS documentation for 9.1.3 is available online, readable online, and is very good and extensive.

Macro programming is "advanced" SAS programming. Start with something much more simple and straight forward.

If you want a report of school schedules, I would recommend reading the documentation about Proc Report, and then experimenting with it.

If you need to analyse or transform your data, use data steps, with set or infile and input commands to retrieve the data. Proc SQL is best used to extract data from a non-SAS database, and to do "joins" and other set related functions when those are needed to filter/organize/associate your data elements.

SAS already understands that you probably have a list of data (observations and variables, rows and columns, records and fields) that you want to process and report. The DATA step is already constructed to access the data and "step" (loop, iterate) through the data. Proc Print and Proc Report already exist to list data to a printer or display or whatever. ODS already exists to provide better generalized structures/methods for outputing in various formats: html, xml, etc. There is no need to explicitly code for much of this stuff.

If I have 1 billion quotes for 1500 trading instruments from 5 financial exchanges, and I want to know the first, last, high, low and count for each instrument from each exchange, then the code is simply

data summary;
set quotes;
by trade_date exchange instrument;
retain first_bid first_ask high_bid high_ask low_bid low_ask count;

if first.instrument then do;
first_bid = bid;
first_ask = ask;
high_bid = bid;
high_ask = ask;
low_bid = bid;
low_ask = ask;
count = 0;
end;

count + 1;

if bid > high_bid then high_bid = bid;
if bid < low_bid then low_bid = bid;
if ask > high_ask then high_ask = ask;
if ask < low_ask then low_ask = ask;

if last.instrument then do;
last_bid = bid;
last_ask = ask;
output;
end;

keep trade_date exchange instrument low_bid high_bid low_ask_high_ask first_bid first_ask last_bid last_ask count;
run;
quit;

( I had wrapped the above code in the [...] stuff but of course this failed and wouldn't display the following comments )

You probably could not do this with your macro scheme because SAS probably would not allow you to store all 1 billion quotes in macro variables, or your computer would probably run out of memory. It would also be a lot more work that what I just wrote.

Note, I don't need to know how many observations or records or rows there are in the quotes dataset.
Note, I don't need to explicitly open and close the dataset.
Note, I don't need to write a loop.

If I didn't care about first and last, I could have even more simply used "proc summary" to do all the work for me with even less coding, less typing. Message was edited by: Chuck

[deleted_user]

I know how to output dataset, but actually I need something like this, because I need to substitute values to the function:
data _null_;
do i=1 to &numrows;
call symput('i', i);
rc=metadata_delobj("omsobj:Login?@Id="||&ids&i||"");
end;
run;

I want to read values from array.
Previous was just an example.

[deleted_user]

This need not be done with any macro variables, and should not be done with any macro variables.
[pre]
data _null_;
set perm.schedule;
where year(begin_date) = 2002;
rc = metadata_delobj("omsobj:Login?@Id=" || trim(student_id) );
run;
[/pre]

As I wrote, before, you are misunderstanding what macros are and what they are for. Message was edited by: Chuck

[Cynthia_sas]

Hi:
I get a bit nervous when I see code to delete metadata objects -- especially if that code contains macro references that just plain won't work.

My "rules" of macro programming are:
1) have a working SAS program that does NOT use macro variables at all -- know what the correct code is to perform your task. Test and make sure that that code works, and does not generate ANY errors in the SAS log and that you do not have any logic errors in the output. Then and only then, move on to step 2.

2) write a generic version of your program, using &MACVAR references and %LET statements for a few test cases, where possible, to make sure that your program will work with Macro variable references. Check things like whether you have double quotes in the right places and that your macro variables are resolving correctly, that numbered macro variables are being built and resolved the right way. Make sure that all your generated code is syntactically correct and that you don't drop semicolons or lose pieces of statements. After #2 version of the program is working, then move to #3.

3) You may be done after #2 -- if you do not need to use any macro-specific language constructs. If you can get by with %LET or CALL SYMPUT or SELECT INTO to build your macro variables, then there's no need to build a macro program. But, if you need macro %IF conditional logic or macro %DO loops or other kinds of things that can only be done in a macro program, then define your macro program with %MACRO/%MEND statements and be sure that you have correctly identified all the parameters that the macro program needs to run properly and then test this macro program by invoking it for all your possible test cases. Then,

4) decide how you're going to run your macro program, as a session compiled macro program, as an autocall macro program or as a stored, compiled macro program and put that version of the program in place and then test again.

It seems to me that you are still in the stage before rule #1 -- you do not have a working SAS program WITHOUT any macro variables -- so it's nearly impossible to make recommendations for how to convert your program to use macro variables -- if you even need a macro program or macro variables at all.

First, when you create a macro variable, as you do with the call symput, that macro variable would not be available to you until AFTER the program in which the macro variable was created. In other words. If you hope to use &i, it would have to be after the step boundary of the data step program in which &i was created. Generally, folks use data _null_ and call symput because they want to do something like this:
[pre]

data _null_;
set sashelp.class end=eof;
if age ge 15 then teentot + 1;
if eof then do;
call symput('numteens',put(teentot,2.0));
end;
run;

ods listing;
option nodate nonumber nocenter;
proc print data=sashelp.class noobs;
title "There are &numteens students who are eligible for driver training.";
run;
[/pre]

&NUMTEENS is created in the data _null_ program, but I cannot use &NUMTEENS inside THAT data step program. In this example, I am using it in a TITLE statement after the RUN statement that ends the DATA _NULL_ program. If I tried to referene that macro variable inside the data step program, I should get this warning:
[pre]
WARNING: Apparent symbolic reference xxxxxxxx not resolved.
[/pre]

where xxxxxxxx is the name of the macro variable that I just created in the CALL SYMPUT.

There's also the issue of your reference to &ids&i -- it's probably not the correct reference. If you were referencing numbered macro variables, then there are a couple of macro variable rules regarding building references ...
the "forward scan" rule and the "rescan" rule that generally dictate that you refer to your numbered macro variables as: &&macvar&i -- which allows the delay of the "front" half of the macro variable from resolving while the "back half" resolves to a number. &&macvar&i would resolve to &macvar1 and then &macvar1, when it is rescanned, would get looked up in the global symbol table.

Whether you need to do anything in a macro program at all is anybody's guess. My recommendation would be to contact Tech Support for help instead of trying to figure out how to delete metadata objects AND how to code macro variable references at the same time.

One thing about SAS datasets that is important to remember is that the SAS DATA step progam is an implied loop... so, if you had this code:
[pre]
data newclass;
set sashelp.class;
newvar = sum(age, height,weight);
run;
[/pre]

Then the variable NEWVAR would be calculated for EVERY observation in SASHELP.CLASS. That's why several people have pointed out that if you have your IDs in a SAS dataset, there may not be a need to load them into numbered macro variables at all.

You have also referred several times to the numbered macro variables as being in an "array" or having a "dimension". I know there are some user group papers out there that use this terminology to refer to macro variables, but I don't like it and I don't agree with that representation. Numbered Macro variables are not an ARRAY structure in the true sense of an ARRAY as a data structure. The macro variables live in memory, in their GLOBAL or LOCAL symbol table and they could just as well be called &FRED, &ETHEL, &LUCY and &RICKY as easily as they could be called &MACVAR1, &MACVAR2, &MACVAR3 and &MACVAR4. (In fact, SAS arrays are not actually permanent data structures, either -- they only exist for the duration of the program which has an ARRAY statement -- but that's a whole other topic for discussion.)

The value of having numbered macro variables, however, is so you could use them within a %DO loop -- which is a language construct that is only allowed in a Macro program -- when you use a %DO loop, then, your "counter" variable automatically becomes a macro variable that you can use to reference within the %DO loop (without a CALL SYMPUT). It is used in a completely different way than the DATA step DO loop and the two forms of iterative processing should not be confused.

If you really want to learn about the SAS Macro facility, which is a totally cool part of SAS, then a few places to start would be the Macro documentation, books by users on the SAS Macro facility or user-group papers on the SAS Macro facility -- like this one: http://www2.sas.com/proceedings/sugi28/056-28.pdf

Since you will need to use the macro facility -someday-, at the bottom of this posting is a complete macro program and invocation that you can copy and run. Study the results in the SAS log carefully and with the results from the PROC PRINTS. This is just one example of using a macro program to generate repetitive code. I could just as easily use a macro program to generate one statement in a program or one piece of one statement.

I'm not sure that you need the macro facility right now -- either macro variables or a macro program -- for your stated purpose. Again, I advise you to contact Tech Support for more help.

cynthia
[pre]

** start the macro definition phase;
%macro prtprod;

proc sql noprint;
select count(distinct prodtype) into :numrows
from sashelp.prdsale;
%let numrows=&numrows;
%put -------> numrows is &numrows;

select distinct prodtype into :pt1-:pt&numrows
from sashelp.prdsale;
quit;

** This PROC SQL step has done 2 things;
** 1) found the count of distinct PRODTYPE values and;
** 2) put those values into numbered macro variables;

** what are the values in the macro variables?;
%do i = 1 %to &numrows;
%put -----> # &i;
%put -----> For pt&i -- PRODTYPE is: &&pt&i;
%put ----->;
%end;

ods listing close;
options nodate nonumber nocenter;

** now build proc print code for every value;
** of PRODTYPE, as stored in pt1-pt&numrows;
%do i = 1 %to &numrows;

%put Starting PROC PRINT;

ods html file="c:\temp\&&pt&i...html" style=sasweb;
proc print data=sashelp.prdsale(obs=10);
title "I is &i -- Prodtype is: &&pt&i " ;
where prodtype = "&&pt&i";
run;
ods html close;
title;
footnote;

%end;
ods listing;

** another way of using a Macro DO loop;
%put Starting ODS HTML for alternate report;
ods listing close;
ods html file="c:\temp\OneReport_TwoPrints.html"
style=sasweb;

%do i = 1 %to &numrows;

proc print data=sashelp.prdsale(obs=5);
title "I is &i -- Prodtype is: &&pt&i " ;
title2 '%DO loop is inside ODS sandwich';
where prodtype = "&&pt&i";
run;
%end;

ods html close;
ods listing;
title;
footnote;

%mend prtprod;
** end the macro definition phase;

***************************************;
***************************************;
** NOTHING happens until the ;
** Macro program is invoked;

** turn on debugging options;
options mprint symbolgen mlogic;

**Now, invoke the macro program just defined;
%prtprod;

** turn off debugging options;
options nomprint nosymbolgen nomlogic;
title; footnote;
[/pre]