BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
grnize
Calcite | Level 5

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;

 

1 ACCEPTED SOLUTION
5 REPLIES 5
Quentin
Super User

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';
The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.
grnize
Calcite | Level 5
I’m stilling getting error of missing )

Reeza
Super User

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
Super User

 

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

 


 

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 720 views
  • 1 like
  • 4 in conversation