Macro List Errors

[JS]

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

Blocked_Email

josh1@gmail.com

josh3@yahoo.com

HAVE

data have;
	input ID$ Date:mmddyy10. email_address: $40. acct: $25.;
	format date mmddyy10.;
	cards;
josh 1/1/2015 josh1@gmail.com 0004
josh 1/2/2015 josh2@yahoo.com 0005
josh 1/3/2015 josh3@yahoo.com 0006
mary 1/4/2015 mary123@aol.com 0007
mary 1/5/2015 mars@blah.com 0008
josh 1/6/2015 josh1@gmail.com 0009
josh 1/7/2015 josh2@yahoo.com 0010
josh 1/8/2015 josh3@yahoo.com 0011
mary 1/9/2015 mary123@aol.com 0012
mary 1/10/2015 mars@blah.com 0008
josh 1/11/2015 josh7@gmail.com 0005
;

WANT

id date email acct block

josh 1/1/2015 josh1@gmail.com 0004 email
josh 1/2/2015 josh2@yahoo.com 0005 notblocked
josh 1/3/2015 josh3@yahoo.com 0006 email
mary 1/4/2015 mary123@aol.com 0007 notblocked
mary 1/5/2015 mars@blah.com 0008 notblocked
josh 1/6/2015 josh1@gmail.com 0009 email
josh 1/7/2015 josh2@yahoo.com 0010 notblocked
josh 1/8/2015 josh3@yahoo.com 0011 email
mary 1/9/2015 mary123@aol.com 0012 notblocked
mary 1/10/2015 mars@blah.com 0008 notblocked
josh 1/11/2015 josh7@gmail.com 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 josh1@gmail.com ,josh2@yahoo.com ,josh3@yahoo.com
_____
22
76
24 ! ,josh7@gmail.com ,mars@blah.com ,mary123@aol.com
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 josh1@gmail.com 0004
josh 1/2/2015 josh2@yahoo.com 0005
josh 1/3/2015 josh3@yahoo.com 0006
mary 1/4/2015 mary123@aol.com 0007
mary 1/5/2015 mars@blah.com 0008
josh 1/6/2015 josh1@gmail.com 0009
josh 1/7/2015 josh2@yahoo.com 0010
josh 1/8/2015 josh3@yahoo.com 0011
mary 1/9/2015 mary123@aol.com 0012
mary 1/10/2015 mars@blah.com 0008
josh 1/11/2015 josh7@gmail.com 0005
;
run;
data blocked;
  blocked_email="josh1@gmail.com"; output;
  blocked_email="josh3@yahoo.com"; 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!