Macro List Errors

[JS]

I'm trying to leverage the macro list function in SAS.

Blocked_Email

[email protected]

[email protected]

HAVE

data have;
	input ID$ Date:mmddyy10. email_address: $40. acct: $25.;
	format date mmddyy10.;
	cards;
josh 1/1/2015 [email protected] 0004
josh 1/2/2015 [email protected] 0005
josh 1/3/2015 [email protected] 0006
mary 1/4/2015 [email protected] 0007
mary 1/5/2015 [email protected] 0008
josh 1/6/2015 [email protected] 0009
josh 1/7/2015 [email protected] 0010
josh 1/8/2015 [email protected] 0011
mary 1/9/2015 [email protected] 0012
mary 1/10/2015 [email protected] 0008
josh 1/11/2015 [email protected] 0005
;

WANT

id date email acct block

josh 1/1/2015 [email protected] 0004 email
josh 1/2/2015 [email protected] 0005 notblocked
josh 1/3/2015 [email protected] 0006 email
mary 1/4/2015 [email protected] 0007 notblocked
mary 1/5/2015 [email protected] 0008 notblocked
josh 1/6/2015 [email protected] 0009 email
josh 1/7/2015 [email protected] 0010 notblocked
josh 1/8/2015 [email protected] 0011 email
mary 1/9/2015 [email protected] 0012 notblocked
mary 1/10/2015 [email protected] 0008 notblocked
josh 1/11/2015 [email protected] 0005 notblocked

Right now my code is set up like this:

I get this error 

24 if upcase(Email_address) in (&BlockedEmail.) then Block='Email';
NOTE: Line generated by the macro variable "BLOCKEDEMAIL".
24 [email protected] ,[email protected] ,[email protected]
_____
22
76
24 ! ,[email protected] ,[email protected] ,[email protected]
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant,
a missing value, iterator, (.

ERROR 76-322: Syntax error, statement will be ignored.

25 ;run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.TEST may be incomplete. When this step was stopped there were 0 observations and 5 variables.
WARNING: Data set WORK.TEST was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):

/*Type2:Email*/
proc sql;
select distinct email_address into :BlockedEmail seperated by ',' notrim
from have
/*where BlockedTypeId=2 ;*/
;quit; 
/*test*/
data Test;
	set have;
    Block='NotBlocked';
	if upcase(Email_address) in (&BlockedEmail.) then Block='Email';
	;run; 
proc print data=test;run

[JS]

Ahhh I figured it out!

It appears that the list must be updated with a quotation mark at the begininng and end to denote the character string search.

proc sql;
select distinct '"'||upcase(email_address)||'"' into :BlockedEmail separated by ','
from blocked_email
/*where BlockedTypeId=2 ;*/
;quit; 
%put &BlockedEmail;
/*test*/
data Test;
	set have;
    Block='NotBlocked';
	if upcase(Email_address) in (&BlockedEmail.) then Block='Email';
	;run; 
proc print data=test;run;

[Reeza]

Use the quote function instead. 

Select quote(upcase(email_address)) into :email_list separated by " "

[JS]

Hey Reeza,

What are your thoughts on using the quotes vs the appending that I used
above? Is one more efficient?

##- Please type your reply above this line. Simple formatting, no
attachments. -##

[Reeza]

One is significantly easier to read 🙂 

Even if I were to concatenate I recommend the CAT family of functions instead of double pipes. 

[JS]

Thank you Reeza.

I am a beginner, so they ALL look very difficult to read (:

I am still trying to get an eye for what is intuitive to other users vs. what is intuitive just for me, and all your help is realy amazing! Thank you so much!

[RW9]

Why are you using macros at all?  What you have is two sets of Data - the dataset of blocked emails, and the dataset of emails.  Thus, the simple Base SAS approach is to merge the blocked dataset onto the email dataset and assign a flag based on that, simpple expandable Base SAS code.  Some examples of the many ways you can achieve this:

data have;
  input id$ date:mmddyy10. email_address: $40. acct: $25.;
  format date mmddyy10.;
  cards;
josh 1/1/2015 [email protected] 0004
josh 1/2/2015 [email protected] 0005
josh 1/3/2015 [email protected] 0006
mary 1/4/2015 [email protected] 0007
mary 1/5/2015 [email protected] 0008
josh 1/6/2015 [email protected] 0009
josh 1/7/2015 [email protected] 0010
josh 1/8/2015 [email protected] 0011
mary 1/9/2015 [email protected] 0012
mary 1/10/2015 [email protected] 0008
josh 1/11/2015 [email protected] 0005
;
run;
data blocked;
  blocked_email="[email protected]"; output;
  blocked_email="[email protected]"; output;
run;
proc sql;
  create table WANT as
  select  A.*,
          case when B.BLOCKED_EMAIL is not null then "email"
               else "notblocked" end as FLAG
  from    WORK.HAVE A
  left join WORK.BLOCKED B
  on      A.EMAIL_ADDRESS=B.BLOCKED_EMAIL;
quit;
/* Or */
proc sql;
  create table WANT as
  select  A.*,
          case when exists(select distinct BLOCKED_EMAIL from WORK.BLOCKED where BLOCKED_EMAIL=A.EMAIL_ADDRESS) then "email"
               else "notblocked" end as FLAG
  from    WORK.HAVE A;
quit;
/* Or */
proc sort data=have;
  by email_address;
run;
data want;
  length flag $20;
  merge have (in=a) blocked (in=b rename=(blocked_email=email_address));
  by email_address;
  if a and b then flag="email";
  else flag="notblocked";
run;

[JS]

That's a great approach to the problem. I was more looking to leverage different functions and expand my base knowlege of what's available in SAS.

All your solutions are certainly very intuitive, and would definitely make excellent work of the query. Thank you!