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
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;
Use the quote function instead.
Select quote(upcase(email_address)) into :email_list separated by " "
One is significantly easier to read 🙂
Even if I were to concatenate I recommend the CAT family of functions instead of double pipes.
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!
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;
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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: