BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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:

NameLVIDVTID
ABSCHLKO11
ABSCHLKO101
VT00
VB10
.........
.........


(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.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

15 REPLIES 15
Jagadishkatam
Amethyst | Level 16

could you please check the log at line 180 and column 322 and see the statements that are causing this error.

Thanks,
Jag
andreas_schmitz
Calcite | Level 5

Sure, when I try:

%put %id_relevant(tab=ABSCHLKO,id=LVID);

the error occurs at the statement:

tmp=find("&&&id._RELEVANT.",cat("&tab.",','));

ballardw
Super User

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_.

andreas_schmitz
Calcite | Level 5

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....

ballardw
Super User

You use multiple %sysfunc, one for each function call. So yes it can get messy and may not be the best approach.

Kurt_Bremser
Super User

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;

andreas_schmitz
Calcite | Level 5

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?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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).

Kurt_Bremser
Super User

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.

andreas_schmitz
Calcite | Level 5

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?

Kurt_Bremser
Super User

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.

andreas_schmitz
Calcite | Level 5

Ah, ok. Now I get the point!

Thanks a lot for your help.

Kurt_Bremser
Super User

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.

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!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 15 replies
  • 18871 views
  • 4 likes
  • 5 in conversation