Hi Community,
I have an amount of tables and several IDs and I need to join the IDs to the tables where it is relevant. Therefore I create a table IDS like this:
Name | LVID | VTID |
---|---|---|
ABSCHLKO | 1 | 1 |
ABSCHLKO1 | 0 | 1 |
VT | 0 | 0 |
VB | 1 | 0 |
... | ... | ... |
... | ... | ... |
(1 means, the ID is relevant, 0 it is not.)
I created the following code segments:
proc sql noprint;
select distinct(Name)
into : LVID_RELEVANT separated by ','
from IDS where LVID=1;
quit;
proc sql noprint;
select distinct(Name)
into : VTID_RELEVANT separated by ','
from IDS where VTID=1;
quit;
So far, everything works fine.
Now, I need to check for several tables, if the ID is relevant.
This is what I did:
data _null_;
X="&LVID_RELEVANT.";
temp=find(X,cat("ABSCHLKO",','));
put temp;
run;
The result is as expected.
As I want to do this for several tables and IDs, I thought about doing this with a macro. I tried the following:
%macro id_relevant(tab= ,id= );
%local result;
data _null_ ;
%put "&&&id._RELEVANT.";
tmp=find("&&&id._RELEVANT.",cat("&tab.",','));
%put tmp;
call symput("result",tmp);
run;
&result
%mend;
When I run the macro: %id_relevant(tab=ABSCHLKO,id=LVID);
I get the correct result but the error:
ERROR 180-322: Statement is not valid or it is used out of proper order.
Any ideas what I did wrong?
Thank you for any help.
OK, NOW you need the "naked" &result so that the result is placed where the macro is called. Just don't expect the macro to work on its own, it needs to be place where the resulting value can be used properly.
could you please check the log at line 180 and column 322 and see the statements that are causing this error.
Sure, when I try:
%put %id_relevant(tab=ABSCHLKO,id=LVID);
the error occurs at the statement:
tmp=find("&&&id._RELEVANT.",cat("&tab.",','));
180-322 signals a syntax error and is usually placed right under the offending code.
180 and 322 are not row and column numbers in this context.
General hint when debugging such things:
options mprint symbolgen mlogic; (the last is more critical with lots of macro %if)
%yourmacrocall.
The log will then show the error statement in relation to the resulting line of code generated by the macro.
Most likely cause of your problem: Excess & in the calls unless you actually have a macrovariable LVID_RELEVANT somewhere.
Are you trying to find something in the string "LVID_RELEVANT" ?
If you are only looking to manipulate a single string you might look at the %sysfunc and avoid the data _null_.
Thanks for the debugging hint.
The macro variable LVID_RELEVANT is created before in the proc sql noprint.
Yes, I only try to check if the table is listed in the corresponding string....
I tried to do it with %sysfunc directly but since my string contains symbols like ',', I only encountered more/others errors....
You use multiple %sysfunc, one for each function call. So yes it can get messy and may not be the best approach.
You are confusing data step processing with macro processing.
%macro id_relevant(tab= ,id= );
%local result;
data _null_ ;
%put "&&&id._RELEVANT."; This %put is executed by the macro processor BEFORE the data step is compiled!
tmp=find("&&&id._RELEVANT.",cat("&tab.",',')); The find function returns a number! This statement is executed during data step processing.
%put tmp; This %put is also executed by the macro processor BEFORE the data step is compiled!
call symput("result",tmp); This is executed DURING data step processing, placing a number in the macro variable result
run;
&result Here the macro processor replaces &result with the contents of the macro variable result, which happens to be a number; a single number is NOT valid SAS code, causing the error message!
%mend;
Thanks a lot!
I removed the % from the put statements.
I got the idea of the result as a single number from this macro, which runs perfectly:
http://www.sascommunity.org/wiki/Tips:Check_if_a_variable_exists_in_a_dataset
The macro also assigns a value 0 or 1 to the result... Why does it work here?
Just to note, the link you sent is 4 years old, so there maybe better ways of doing things. And you can always check the metadata tables in SAS if you need to see if something exists (SASHELP.VTABLE/VCOLUMN).
You probably want to replace the simple &result with %put &result;
Still better (IMO) would be to use put tmp=; in the data _null_ step, instead of storing the result in a macro variable with call symput.
I replaced the &result with %put &result;
Now the macro itself runs without any errors. Thanks for that!
But If I would like to use it, like this:
%if %id_relevant(tab=ABSCHLKO1,id=LVID)=0 %then
inside some code, I get the error:
ERROR: Required operator not found in expression: %id_relevant(tab=ABSCHLKO1,id=LVID)=0
What's happening now?
OK, NOW you need the "naked" &result so that the result is placed where the macro is called. Just don't expect the macro to work on its own, it needs to be place where the resulting value can be used properly.
Ah, ok. Now I get the point!
Thanks a lot for your help.
Just keep in mind that the principal task of the macro processor is to generate text for feeding back to the "main" SAS interpreter. So some macros may be done for a very specific point in the SAS code, and can not be tested sensibly outside of that situation.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.