BookmarkSubscribeRSS Feed
JS
Obsidian | Level 7 JS
Obsidian | Level 7

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
7 REPLIES 7
JS
Obsidian | Level 7 JS
Obsidian | Level 7

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

Use the quote function instead. 

Select quote(upcase(email_address)) into :email_list separated by " "
JS
Obsidian | Level 7 JS
Obsidian | Level 7
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
Super User

One is significantly easier to read 🙂 

 

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

JS
Obsidian | Level 7 JS
Obsidian | Level 7

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7 JS
Obsidian | Level 7

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1260 views
  • 4 likes
  • 3 in conversation