proc sql string variable problem

Reply
N/A
Posts: 0

proc sql string variable problem

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
Frequent Contributor
Posts: 91

Re: proc sql string variable problem

In your second macro definition, try:
%macro loop_vals(lval=g_dist,delim=*);
instead of
%macro loop_vals(lval=&g_dist,delim=*);
N/A
Posts: 0

Re: proc sql string variable problem

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?
SAS Super FREQ
Posts: 8,814

Re: proc sql string variable problem

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]
N/A
Posts: 0

Re: proc sql string variable problem

Hi Cynthia,

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

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
Ask a Question
Discussion stats
  • 4 replies
  • 499 views
  • 0 likes
  • 3 in conversation