in: and macro not working

[saslove]

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? 

 

[Astounding]

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.

[saslove]

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

[Reeza]

---

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

[Reeza]

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.

[Tom]

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.

[Reeza]

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.

[PGStats]

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;

[SuryaKiran · Accepted solution]

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;

[saslove]

This works perfectly! Thank you SuryaKiran!