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