topic Re: Reading a Macro list in proc sql in SAS Programming

Reading a Macro list in proc sql

grnize — Mon, 26 Sep 2022 21:01:49 GMT

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;

Re: Reading a Macro list in proc sql

Quentin — Mon, 26 Sep 2022 21:13:51 GMT

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';

Re: Reading a Macro list in proc sql

Kurt_Bremser — Mon, 26 Sep 2022 21:14:21 GMT

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

Re: Reading a Macro list in proc sql

Reeza — Mon, 26 Sep 2022 21:28:30 GMT

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;

Re: Reading a Macro list in proc sql

grnize — Mon, 26 Sep 2022 22:09:31 GMT

I’m stilling getting error of missing )

Re: Reading a Macro list in proc sql

Reeza — Mon, 26 Sep 2022 22:45:17 GMT

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.