Reading a Macro list in proc sql

[grnize]

I want to read these two macro into the proc sql. I keep getting an error that a select statement is missing; I put a smaple dataset at the below to use

%LET ZIPCODE_LIST2=('95201','95202','95203','95204','95205','95206','95207','95208','95209','95210','95211','95212','95213','95214','95215','95219',
'95267','95269','95296','95297');

%let yrmo_b2=202208;

proc sql;
CREATE TABLE attrib2 AS
seLECT *,
case index
when '211A' then '212A'
when '211B' then '212B'
when '211C' then '212C'
when '211D' then '212D'
when '211E' then '212E'
else index2 end
FROM attrib1
WHERE zipcode IN (&ZIPCODE_LIST2)
and yrmo  in ( 202207 ,&yrmo_b2);

quit;

DATA ACO1;
input ID $ INDEX $ YRMO zipcode $;
DATALINES;
10528076 211D 202207 95204
10588202 211E 202207 95212
10590191 211B 201802 95336
10593162 211D 202208 95219
10593777 211D 202204 95201
10593787 211D 201802 95336
10594270 211C 201802 95376
10594273 211C 202208 95214
10595143 211D 201802 95336
10596446 211E 202208 95336
10597124 211D 202207 95201
10597867 211D 202206 95376
10598211 211A 202201 95249
10600934 211D 201802 95202
10600935 211D 202205 95336
10601192 211D 202205 95213
10689802 211A 201802 95204
10702706 211C 202208 95204
10735256 211E 202207 95201
10750858 211D 202208 95210
RUN;

[Quentin]

Your list has parentheses in it:

%LET ZIPCODE_LIST2=('95201','95202','95203','95204','95205','95206','95207','95208','95209','95210','95211','95212','95213','95214','95215','95219',
'95267','95269','95296','95297');

Your where clause also has parentheses:

WHERE zipcode IN (&ZIPCODE_LIST2)

This results in too many parentheses. I would remove the parentheses from your list, i.e. change to:

%LET ZIPCODE_LIST2='95201','95202','95203','95204','95205','95206','95207','95208','95209','95210','95211','95212','95213','95214','95215','95219',
'95267','95269','95296','95297';

[Kurt_Bremser · Accepted solution]

Your macro variable already contains brackets, so you do not need
additional ones when you call it.

[grnize]

I’m stilling getting error of missing )

[Reeza]

You need to post your code and log if you're still getting errors. 

Note the code I posted was tested and ran without issues. 

[Reeza]

• The CASE statement is missing a new name assigned 
• Extra parenthesis on the zipcode_list2 macro variable
• Missing semicolon on the data step to create your sample data
• Assumes ACO1 is the sample input data set (not the same name as in the code)

DATA ATTRIB1;
input ID $ INDEX $ YRMO zipcode $;
DATALINES;
10528076 211D 202207 95204
10588202 211E 202207 95212
10590191 211B 201802 95336
10593162 211D 202208 95219
10593777 211D 202204 95201
10593787 211D 201802 95336
10594270 211C 201802 95376
10594273 211C 202208 95214
10595143 211D 201802 95336
10596446 211E 202208 95336
10597124 211D 202207 95201
10597867 211D 202206 95376
10598211 211A 202201 95249
10600934 211D 201802 95202
10600935 211D 202205 95336
10601192 211D 202205 95213
10689802 211A 201802 95204
10702706 211C 202208 95204
10735256 211E 202207 95201
10750858 211D 202208 95210
;;;; /*add semicolon to have sample data read in correctly*/
RUN;


%LET ZIPCODE_LIST2=('95201','95202','95203','95204','95205','95206','95207','95208','95209','95210','95211','95212','95213','95214','95215','95219',
'95267','95269','95296','95297');

%let yrmo_b2=202208;

 

proc sql;
CREATE TABLE attrib2 AS
seLECT *,
case index
when '211A' then '212A'
when '211B' then '212B'
when '211C' then '212C'
when '211D' then '212D'
when '211E' then '212E'
else '' end as index2  /*end the case statement properly*/
FROM attrib1
WHERE zipcode IN &ZIPCODE_LIST2 /*remove parenthesis from code*/
and yrmo  in (202207, &yrmo_b2);

quit;

 


 

---

@grnize wrote:

I want to read these two macro into the proc sql. I keep getting an error that a select statement is missing; I put a smaple dataset at the below to use

%LET ZIPCODE_LIST2=('95201','95202','95203','95204','95205','95206','95207','95208','95209','95210','95211','95212','95213','95214','95215','95219',
'95267','95269','95296','95297');

%let yrmo_b2=202208;

proc sql;
CREATE TABLE attrib2 AS
seLECT *,
case index
when '211A' then '212A'
when '211B' then '212B'
when '211C' then '212C'
when '211D' then '212D'
when '211E' then '212E'
else index2 end
FROM attrib1
WHERE zipcode IN (&ZIPCODE_LIST2)
and yrmo  in ( 202207 ,&yrmo_b2);

quit;

DATA ACO1;
input ID $ INDEX $ YRMO zipcode $;
DATALINES;
10528076 211D 202207 95204
10588202 211E 202207 95212
10590191 211B 201802 95336
10593162 211D 202208 95219
10593777 211D 202204 95201
10593787 211D 201802 95336
10594270 211C 201802 95376
10594273 211C 202208 95214
10595143 211D 201802 95336
10596446 211E 202208 95336
10597124 211D 202207 95201
10597867 211D 202206 95376
10598211 211A 202201 95249
10600934 211D 201802 95202
10600935 211D 202205 95336
10601192 211D 202205 95213
10689802 211A 201802 95204
10702706 211C 202208 95204
10735256 211E 202207 95201
10750858 211D 202208 95210
RUN;

---