I have a set of observations where: when a rule is activated, it simply populated a field as a string with a semi-colon dilineator. The rules can be out of order, some are long, some are short, etc. etc.   Is there a way to cross tabulate on a string of texts?   Have:    Observation Pass Rule_String 1 1 Rule A;Rule C;Rule D 2 0 Rule C;Rule B 3 1 Rule A;Rule B Want:     Rule Count Pass A 2 2 B 2 1 C 2 1 D 1 1 ... View more

  Is anyone familiar with how to conduct a loop in a SQL step?   I'm trying to figure out how to loop through two sets of values in SQL. I realize that the way I'm currently doing the query (just manually writing the code multiple times) also works, but I was wondering if there's a better way to do it.     Have:   proc sql; Select ID, home_phone, work_phone, cell_phone case when home_phone in ("") then "_" when home_phone in ("0") then "0" else "H" end as home_present case when work_phone in ("") then "_" when work_phone in ("0") then "0" else "W" end as work_present, case when cell_phone in ("") then "_" when cell_phone in ("0") then "0" else "C" end as cell_present from TABLE ;quit; And I want something like this (but actually works lol )   [Letter loop] = H,W,C; [Number loop] = home,work,cell; proc sql; Select ID, [Number Loop]_phone case when [Number Loop]_phone in ("") then "_" when [Number Loop]_phone in ("0") then "0" else [Letter Loop] end as [Number Loop]_present from TABLE ;quit;   ... View more

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 ... View more