Using WHERE to find a variable value in an SQL macro generated list

[robulon]

Hi,

I'm using SAS Univeristy Edition and I've created a new variable by concatenating two existing character variables and have then used proc SQL to create a macro variable list of the concatenated values when other criteria is met.

What I'm then trying to do is identify variables from a different dataset (where I have also concatenated the same two variables) that match one of the values form the macro list. I thought I should be able to do this using the FINDW function by specifying

where findw(&a_rt_list,a_rt) > 0; 

but am getting a syntax error when parsing the WHERE clause. Any advice would be gratefully received, I've included the code and log detail below.

proc sql;
	create table alarm_test_4 as
	select
		 a.*
		,compress(a.alarms)||compress(a.racetype) as a_rt
	from alarm_test_2 as a,
	alarm_test_b as b
	where a.alarms = b.alarms;
quit;

proc sql noprint;
	select 
		a_rt
	into :a_rt_list separated by ' '
	from alarm_test_4
	where bfvo_roi >= 1.1;
quit;

%put &a_rt_list;

data test;
	set dailies_remerge (keep= alarms racetype);
	where not missing (alarms);
	a_rt = compress(alarms)||compress(racetype);
run;

data test_2;
	set test;
		where findw(&a_rt_list,a_rt) > 0;
run;

62         proc sql;
 63         create table alarm_test_4 as
 64         select
 65          a.*
 66         ,compress(a.alarms)||compress(a.racetype) as a_rt
 67         from alarm_test_2 as a,
 68         alarm_test_b as b
 69         where a.alarms = b.alarms;
 NOTE: Table USER.ALARM_TEST_4 created, with 31 rows and 12 columns.
 
 70         quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.09 seconds
       cpu time            0.02 seconds
       
 
 71         
 72         proc sql noprint;
 73         select
 74         a_rt
 75         into :a_rt_list separated by ' '
 76         from alarm_test_4
 77         where bfvo_roi >= 1.1;
 78         quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 79         
 80         %put &a_rt_list;
 +DGHURDLE +WCHASE +WFLAT -B>CHASE -B>FLAT 7BCHASE BMAW BMCHASE BMFLAT BMHURDLE BMNHFLA TPAW TPCHASE TPFLAT TPNHFLA
 81         
 82         data test;
 83         set dailies_remerge (keep= alarms racetype);
 84         where not missing (alarms);
 85         a_rt = compress(alarms)||compress(racetype);
 86         run;
 
 NOTE: There were 126 observations read from the data set USER.DAILIES_REMERGE.
       WHERE not MISSING(alarms);
 NOTE: The data set USER.TEST has 126 observations and 3 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.06 seconds
       cpu time            0.02 seconds
       
 
 87         
 88         data test_2;
 89         set test;
 90         where findw(&a_rt_list,a_rt) > 0;
 NOTE: Line generated by the macro variable "A_RT_LIST".
 90         +DGHURDLE +WCHASE +WFLAT -B>CHASE -B>FLAT 7BCHASE BMAW BMCHASE BMFLAT BMHURDLE BMNHFLA TPAW TPCHASE TPFLAT TPNHFLA
                                                      _
                                                      22
                                                      76
 ERROR: Syntax error while parsing WHERE clause.
 ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, ), *, **, +, ',', -, /, <, <=, <>, =, >, >=, AND, EQ, GE, 
               GT, LE, LT, NE, NOT, OR, ^, ^=, |, ||, ~, ~=.  
 
 ERROR 76-322: Syntax error, statement will be ignored.
 
 91         run;
 
 NOTE: The SAS System stopped processing this step because of errors.

Many thanks,

Rob

[Kurt_Bremser]

findw() expects strings, so you must use double quotes around your macro variable.

[Tom]

Seems pretty clear in the log. So you can see in the log that your macro variable has something like

+DGHURDLE +WCHASE +WFLAT

So when you use like this:

where findw(&a_rt_list,a_rt) > 0;

You end up generating code that looks like this.

where findw(+DGHURDLE +WCHASE +WFLAT,a_rt) > 0;

which SAS properly rejects as gibberish.

How would you change that last statement to be valid syntax?  

You would need to make the first argument to FINDW() be a string expression.  So it needs quotes.

where findw("&a_rt_list",a_rt) > 0;

[Reeza]

Although you can technically do this, and it's obviously possible, I suspect there's an alternative method that may be more efficient. 

Something like the following perhaps?

proc sql;
create table vars_in as
select *
from table1 
where concatenated_key not in (select concatenated_key from table2);
quit;

[robulon]

Thanks Tom & Kurt for your help, and thanks Reeza, I've no doubt that what you say is correct but I'm just trying to get to grips with creating macro variables using proc sql.

That said, I'm still struggling with this and can't work out why. Using the same code as previously, I've tried creating seperate macro variables and that works fine: -

 62         /* Create sql macro variables from profitable alarms */
 63         proc sql noprint;
 64         select
 65         a_rt
 66         into :a_rt_list_1 - :a_rt_list_15
 67         from alarm_test_4
 68         where bfvo_roi >= 1.1;
 69         quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       cpu time            0.01 seconds
       
 
 70         
 71         %put &a_rt_list;
 AWTP CHASETP FLATTP NHFLATP AWBM CHASEBM FLATBM HURDLEBM NHFLABM CHASE-B> FLAT-B> CHASE+W FLAT+W CHASE7B HURDLE+DG
 72         
 73         data test;
 74         set dailies_remerge (keep= alarms time racetype);
 75         where not missing (alarms);
 76         a_rt = compress(racetype)||compress(alarms);
 77         run;
 
 NOTE: There were 126 observations read from the data set USER.DAILIES_REMERGE.
       WHERE not MISSING(alarms);
 NOTE: The data set USER.TEST has 126 observations and 4 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.14 seconds
       cpu time            0.01 seconds
       
 
 78         
 79         data test_2;
 80         set test;
 81         where a_rt in ("&a_rt_list_1"  "&a_rt_list_2"  "&a_rt_list_3"  "&a_rt_list_4"  "&a_rt_list_5"  "&a_rt_list_6"
 82         "&a_rt_list_7"  "&a_rt_list_8"  "&a_rt_list_9"  "&a_rt_list_10"  "&a_rt_list_11"  "&a_rt_list_12"
 83         "&a_rt_list_13"  "&a_rt_list_14"  "&a_rt_list_15");
 84         run;
 
 NOTE: There were 1 observations read from the data set USER.TEST.
       WHERE a_rt in ('AWBM', 'AWTP', 'CHASE+W', 'CHASE-B>', 'CHASE7B', 'CHASEBM', 'CHASETP', 'FLAT+W', 'FLAT-B>', 'FLATBM', 
       'FLATTP', 'HURDLE+DG', 'HURDLEBM', 'NHFLABM', 'NHFLATP');
 NOTE: The data set USER.TEST_2 has 1 observations and 4 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.07 seconds
       cpu time            0.01 seconds

But when I'm creating the list that contains the same values using the 'separated by' method, it doesn;t work: -

 62         proc sql noprint;
 63         select
 64         a_rt
 65         into :a_rt_list separated by ' '
 66         from alarm_test_4
 67         where bfvo_roi >= 1.1;
 68         quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 69         
 70         %put &a_rt_list;
 AWTP CHASETP FLATTP NHFLATP AWBM CHASEBM FLATBM HURDLEBM NHFLABM CHASE-B> FLAT-B> CHASE+W FLAT+W CHASE7B HURDLE+DG
 71         
 72         data test;
 73         set dailies_remerge (keep= alarms racetype horse);
 74         where not missing (alarms);
 75         a_rt = compress(racetype)||compress(alarms);
 76         run;
 
 NOTE: There were 126 observations read from the data set USER.DAILIES_REMERGE.
       WHERE not MISSING(alarms);
 NOTE: The data set USER.TEST has 126 observations and 4 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.05 seconds
       cpu time            0.01 seconds
       
 
 77         
 78         data test_2;
 79         set test;
 80         where findw("&a_rt_list",a_rt) > 0;
 81         run;
 
 NOTE: There were 0 observations read from the data set USER.TEST.
       WHERE FINDW('AWTP CHASETP FLATTP NHFLATP AWBM CHASEBM FLATBM HURDLEBM NHFLABM CHASE-B> FLAT-B> CHASE+W FLAT+W CHASE7B 
       HURDLE+DG', a_rt)>0;
 NOTE: The data set USER.TEST_2 has 0 observations and 4 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.06 seconds
       cpu time            0.01 seconds

Whether it has any specific relevance or not, I'm not sure but the characters that the code is finding in the first iteration but not the second is FLAT-B>.

Again, any suggestions gratefully received.

Rob

[Kurt_Bremser · Accepted solution]

Either use the "R" modifier in the findw() function, or use trim() to get rid of trailing blanks:

where findw("&a_rt_list",a_rt,' ','R') > 0;

where findw("&a_rt_list",trim(a_rt)) > 0;

[robulon]

Brilliant, thanks Kurt

[Tom]

Making a lot of individual macro variables is probably not effecient. (unless you expect lots of value, in which case use a join instead of macro variables).

proc sql noprint;
select quote(trim(a_rt))
  into :a_rt_list separated by ' '
  from alarm_test_4
  where bfvo_roi >= 1.1
;
quit;
...
where a_rt in (&a_rt_list)