BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hi there,

Trying to create a list of unique records and then do some function for each of them. So far I have the following code.



[pre]
%macro dist_vals (dataset=work.sms,var=analyte_name,delim='*');
/*
Creates a delimiter separated string of unique values for a
specified column.
INPUT: dataset, column name to be sorted by, delimiter.
RETURNS:delimiter separated string of unique values assigned
to global variable g_dist.
*/
%local dataset var delim;
%global g_dist;
proc sql noprint;
select distinct &var
into :g_dist
separated by &delim
from &dataset;
quit;

%mend dist_vals;


%macro loop_vals(lval=&g_dist,delim=*);
/*
Loops through the string of analytes assigned to lval and
outputs to
*/

%local i value lval;
%let i=1;
%let lval=&lval;
%do %until(&value=);
%let value=%scan(&lval,&i,&delim);
/* %sms_output(var=&value,outfile=&outpath);*/
%let i=%eval(&i+1);
%end;
%mend loop_vals;

%loop_vals();


[/pre]


%put &g_dist;

yeilds xxxx*xxxx*xxxxx*xxxxx

Which fails on the second to last loop, so misses out the last substring with the following error

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition
was: &value=
ERROR: The condition in the %DO %UNTIL loop, , yielded an invalid or missing value, . The macro will stop executing.
ERROR: The macro LOOP_VALS will stop executing.

However if I use

%let x=xxxx*xxxx*xxxxx*xxxxx

then it works. I can't figure out why this would be, any suggestions? What is the difference between the variable x and the variable g_dist that was created with proc sql?

thanks
4 REPLIES 4
advoss
Quartz | Level 8
In your second macro definition, try:
%macro loop_vals(lval=g_dist,delim=*);
instead of
%macro loop_vals(lval=&g_dist,delim=*);
deleted_user
Not applicable
Thanks for the response, but unfortunately it doesnt work. I would have thought that passing the parameter by reference would be okay, but in this case lval resolves to the string g_dist, so I need to keep the ampersand in there.

Any other suggestions?
Cynthia_sas
SAS Super FREQ
Hi:
If you have variables in the GLOBAL symbol table, then you do NOT need to pass them at all to the local macro or called macro program. You can just reference them. However, you have to be sure that they are IN the GLOBAL symbol table. ANY macro variable referenced inside parentheses, on a %MACRO definition statement, by default, becomes a LOCAL macro variable and when the LOCAL macro variable ends, the symbol table that holds the LOCAL macro variables goes away.

In your original example, you say that you are calling a "function", but your call to %sms_output is a macro call, not a function call. All %sms_output will do for you is to generate code to be placed on the INPUT stack for compilation and execution.

Here are some comments about your macro program:
1) delim='*' should change. You have: [pre]
%macro dist_vals (dataset=work.sms,var=analyte_name,delim='*');
[/pre]
and then later, you have this:[pre]
separated by &delim
[/pre]

In my experience, it is never a good idea to "prequote" a macro variable. This may seem like a fix and may indeed stop some error messages from occurring, but in the long run, I find this to be the preferable coding technique:[pre]
%macro dist_vals (dataset=work.sms,var=analyte_name,delim=*);
[/pre]
and then:[pre]
separated by "&delim"
[/pre]

If you find that you need more "protection" for anything (characters, operators, quoted strings) that you need to pass into your program, then your best approach is to use the Macro Facility "quoting" functions. I think of these "quoting" functions like Harry Potter's cloak of invisibility for macro variables -- because they protect certain characters and symbols from being seen and acted on at the wrong time in the compile/execute process.

2) The reason that your loop seems to fail, is that the %DO %UNTIL is evaluated at the BOTTOM of the loop. If you had used a %DO %WHILE loop or a counted
%DO loop, then your logic would be different. However, to avoid getting that error message, you need a %IF to test whether VALUE is NULL or not. My test code below uses a %IF/%END block to take care of that.

I have placed %PUT statements at strategic places in the code. Along with the options I specify, they WILL generate a LOT of output in the LOG. Careful study of that output may help you understand how GLOBAL and LOCAL variables are available to you. Especially note how I use &G_DIST to calculate &VALUE2 without "passing" it into the %LOOP_VAL macro. In fact, note how I have removed &LVAL and &G_DIST from the macro definition. Since you seemed to want to invoke %sms_output inside the %DO loop, but did not give an example of what it would do, I made up an %sms_output macro program to do a proc print on sashelp.class and use the &VALUE in the WHERE clause for the proc print. I also used &VALUE to set &OUTPATH.

This may not have been the kind of thing that you intended your %sms_output macro to do, but I feel it suffices to show a complete, working example.

3) You might be breaking down this task into too many "mini" macro programs. A different possibility, since you want to reuse the same DELIM, DATASET and value for G_DIST might be to roll this into 1 comprehensive macro program instead of 3 little programs.

Since the code is getting lengthy and since you may have further debugging challenges when you go to convert my working example to your purposes and your data, I highly recommend that you consider working with Tech Support on this task. The lengthier the code gets, the harder it will be to debug and offer help on the forum. Macro code is especially verbose and for most macro problems, code snippets just won't do.

cynthia
[pre]
**** changed macro programs and invocation examples;
options mprint mlogic symbolgen;

**1) define DIST_VALS macro program;
%macro dist_vals (dataset=work.sms,var=analyte_name,delim=*);
%put -----> inside dist_vals;
%put _user_;

** set g_dist and wantdsn to GLOBAL for later use;
%local dataset var delim;
%global g_dist wantdsn;
%let wantdsn=&dataset;
%put ----> after GLOBAL and LET: wantdsn=&wantdsn;

** use quotes around DELIM in the SQL clause, where they belong;
proc sql noprint;
select distinct &var
into :g_dist
separated by "&delim"
from &dataset;
quit;
%put ----> after SQL: g_dist=&g_dist ;

%mend dist_vals;

%macro loop_vals(delim=*);
%put -----> inside loop_vals;
%put _user_;
%put -----> ;

** you probably do not need LVAL to be LOCAL at all;
** but if you WANT it to be LOCAL, you can just assign it;
** the current value of G_DIST;
%local i value lval;
%let i=1;

%let lval=&g_dist;
%do %until(&value=);
%let value=%scan(&lval,&i,&delim);
%let value2=%scan(&g_dist,&i,&delim);
** This IF/END prevents the error message on the last;
** loop through the DO UNTIL macro loop;
%if &value ne %then %do;

%let outpath = c:\temp\&value..html;
%put -----> after LET for outpath= &outpath;
%put -----> i=&i ~~~ value=&value ~~~ value2=&value2;
%put ----> if g_dist IS global, that means you can use it here;
%put ----> instead of lval -- unless you explicitly WANT lval;

%sms_output(var=&value,outfile=&outpath);

%end;

%let i=%eval(&i+1);
%end;
%mend loop_vals;

%macro sms_output(var=,outfile=);
%put -----> inside sms_output;
%put _user_;
%put -----> ;

ods html file="&outfile";
proc print data=&wantdsn;
where name = "&var";
run;
%mend sms_output;

** NOW invoke the 2 main macros;
** SMS_OUTPUT will be invoked inside LOOP_VALS;
%dist_vals(dataset=sashelp.class, var=name, delim=*);

%loop_vals();

options nomprint nomlogic nosymbolgen;

[/pre]
deleted_user
Not applicable
Hi Cynthia,

Many thanks for taking the time to respond. Thanks for the tip on not prequoting macro variables - will keep this in mind 🙂

Finally managed to get this working after reading a paper that you linked in another discussion about creating arrays in macros. Using qscan instead of scan seemed to do the trick.


[pre]
%macro loop_vals(lval=&g_dist,delim=*);
/*
Loops through the string of analytes assigned to lval and
outputs to
*/

%local i value lval;
%let i=1;
%let lval=&lval;
%do %until(&value=);
%let value=%qscan(&lval,&i,&delim);
%sms_output(var=&value,outfile=&outpath);
%let i=%eval(&i+1);
%end;

%mend loop_vals;
[/pre]


Many thanks

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!

New Learning Events in April

 

Join us for two new fee-based courses: Administrative Healthcare Data and SAS via Live Web Monday-Thursday, April 24-27 from 1:00 to 4:30 PM ET each day. And Administrative Healthcare Data and SAS: Hands-On Programming Workshop via Live Web on Friday, April 28 from 9:00 AM to 5:00 PM ET.

LEARN MORE

Discussion stats
  • 4 replies
  • 1076 views
  • 0 likes
  • 3 in conversation