topic Re: in: and macro not working in SAS Programming

in: and macro not working

saslove — Fri, 09 Nov 2018 23:11:02 GMT

I am trying to capture a phrase from my dataset that includes a bunch of terms. I am using PROC SQL to get the list of variables separated by a comma. I need to use this phrase in a SAS function with IN: (which I believe finds instances that carry the relevant string). But the code gives me 0 observations despite having variables in my data.

SAS DATA STEP:

proc sql noprint;
select upcase("'"||STRIP(toxxx)||"'") into:neurolist separated by ', '

from test;quit;
%put &neurolist;

LOG:

Macro variable NEUROLIST resolves to 'CONFUSION', 'DIZZINESS', 'HEADACHE', 'INSOMNIA', 'MEMORY IMPAIRMENT', 'TREMOR'
6767 %put &neurolist;
'CONFUSION', 'DIZZINESS', 'HEADACHE', 'INSOMNIA', 'MEMORY IMPAIRMENT', 'TREMOR'.

The codes doesn't get me an error. It works, but doesn't have any observations.

NOTE: There were 227 observations read from the data set WORK.MEDS_NEURO.
NOTE: The data set WORK.NEURODEX has 0 observations and 7 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.03 seconds

Please see attachment for my dataset that has the term "HEADACHE", but my code doesn't capture it. Any idea why?

Re: in: and macro not working

Astounding — Fri, 09 Nov 2018 23:17:44 GMT

You've shown the log from the part of the program that works. That's a good start.

If you want help in debugging the program, you will need to show the log from the part of the program that doesn't work.

Re: in: and macro not working

Tom — Fri, 09 Nov 2018 23:29:07 GMT

Where is the data step code? Also show examples of the data you are searching.

Make sure to use the Insert Code or Insert SAS Code icons on the menu to open a pop-window to enter your text so that formatting is preserved.

Re: in: and macro not working

Reeza — Fri, 09 Nov 2018 23:32:01 GMT

The code you're showing creates a macro variable and doesn't do anything beyond that, its not set up to do anything. If you have other code, please post that.

Re: in: and macro not working

PGStats — Fri, 09 Nov 2018 23:33:32 GMT

I think you don't need the macro variable at all. Please try:

proc sql;
create table neurodex as
select a.*, b.toxxx
from meds_neuro as a, test as b
where a.cmindco contains upcase(strip(b.toxxx));
quit;

Re: in: and macro not working

saslove — Sat, 10 Nov 2018 01:10:10 GMT

As I had mentioned in the post , the program executed but gave me no observations. Log is clear.

Re: in: and macro not working

Reeza — Sat, 10 Nov 2018 01:14:26 GMT

@saslove wrote:
As I had mentioned in the post , the program executed but gave me no observations. Log is clear.

You didn't show any code and there's no attachment. You're probably doing something wrong but since we can't see the code, data or log your guess is as good as ours at the moment.

Re: in: and macro not working

Reeza — Sat, 10 Nov 2018 01:16:35 GMT

Actually you're misunderstanding how IN works, it won't work how you've designed it here - it matches from the beginning of the strings and your text is in the middle. You can use LIKE instead with SQL - no need to create macro variables then. There's some examples of how to do that on here in previous questions.

Re: in: and macro not working

SuryaKiran — Sat, 10 Nov 2018 14:52:08 GMT

Your values have to exactly match how they exists in the dataset ie. HEADACHE is not equal to NEUROTOXICITY(HEADACHE).

So you may have to use index() or find() or prxmatch() to find a word in a string.

proc sql noprint;
select STRIP(toxxx) into:neurolist separated by '|'

from test;quit;
%put &neurolist;

proc sql;
create table neurodex as
select a.*, b.toxxx
from meds_neuro as a, test as b
where prxmatch("m/&neurolist/io",a.cmindco)>0;
quit;

Re: in: and macro not working

saslove — Mon, 12 Nov 2018 21:14:01 GMT

This works perfectly! Thank you SuryaKiran!