Hi,   I am currently doing some address parsing/token creation using regular expressions. I am looking for ways to improve my speed in the validation code. I think nesting the CASE statements or an effective DO loop could improve things. Below is the PROC SQL and the alternate data step, however I think there might be faster ways. I have some attempting sub setting, however I need something tighter / dynamic or easy to edit. Once a match is found, I would like to exclude those observations from the next PRXMATCH, and so on and so on until I can identify the matches by match type, and also the non matches. The parsing code post validation runs smoothly and quickly, so I am bottlenecked at this stage. Any assistance would be greatly appreciated.   The SQL is like so -   PROC SQL; CREATE TABLE ADDRESS_VALIDATE AS SELECT DISTINCT ADDRESS, (CASE WHEN PRXMATCH ("/&AD_PATTERN_1/",ADDRESS)     THEN 'Y' ELSE '' END) AS PATTERN_A1,   (CASE WHEN (CALCULATED PATTERN_A1) IS MISSING AND     PRXMATCH ("/&AD_PATTERN_2/",ADDRESS)       THEN 'Y' ELSE '' END) AS PATTERN_A2   FROM ADDRESS ;quit;   I believe it runs slowly due to the continuous passes over the variable when we already have a match. I have done some ranking and the first PRXMATCH is the highest result.   I tried to use the data step   data ADDRESS_VALIDATE; set ADDRESS; ADPATTERN_A1 = prxparse("/&AD_PATTERN_1/"); ADPATTERN_A2 = prxparse("/&AD_PATTERN_2/"); MATCH = 'N';   if PRXMATCH (ADPATTERN_A1,ADDRESS) then do;   PATTERN_A1 = 'Y';   MATCH = 'Y'; end;   call prxfree (ADPATTERN_A1);   if MATCH = 'N' then do;  if PRXMATCH (ADPATTERN_A2,ADDRESS) then do;    PATTERN_A2 = 'Y';    MATCH = 'Y';  end; end;   call prxfree (ADPATTERN_A2); DROP ADPATTERN_A1-ADPATTERN_A2; run;     but not much difference in run time.   ... View more

I would like to find an easier solution to a problem I am having. I can manage this task, however it's though several steps outside SAS (SQL LIMIT). At best I don't get all the observations correctly in SAS alone. I have used a POINT and SET however I can't get the BY variables working. Would be good to have a data step or PROC SQL to be able to achieve this in SAS alone. The code I have modified is below. Currently, it does not discriminate between the ID, it just follows the order of the set. data WANT; set HAVE  nobs=nobs; if FLAG = 'Y' then do;   current = _N_;   prev = current - 1;   next = current + 1; if prev > 0 then do;     set HAVE point = prev;       POSTION = 'PREVIOUS';       output;    end; set HAVE point = current;      POSTION = 'CURRENT';        output; if next <= nobs then do;    set HAVE point = next;     POSTION = 'NEXT';     output;   end; end; run;   I have a table with multiple ID's. Within each ID there is an observation with a flag. OBS ID FLAG 1 RED 2 RED 3 RED Y 4 RED 5 RED 6 BLUE Y 7 BLUE 8 GREEN 9 GREEN Y 10 PURPLE 11 PURPLE 12 PURPLE 13 PURPLE Y 14 PURPLE 15 PURPLE I would like to locate and tag the current, previous and next observation based on OBS order (or dates) and the ID as sorted above. The output would look like below. OBS ID FLAG POSTION 1 RED 2 RED PREVIOUS 3 RED Y CURRENT 4 RED NEXT 5 RED 6 BLUE Y CURRENT 7 BLUE NEXT 8 GREEN PREVIOUS 9 GREEN Y CURRENT 10 PURPLE 11 PURPLE 12 PURPLE PREVIOUS 13 PURPLE Y CURRENT 14 PURPLE NEXT 15 PURPLE Any help would be greatly appreciated. ... View more

Hi, I'm new to SAS, just completed Programming 1 and VA Fast Track and gained access to EG. While I wait for The Little SAS Book for Enterprise to arrive in the mail, I have a few questions about joining tables in EG. Please correct my nomenclature or terminology when possible, so I can improve my knowledge of this platform. If anything is unclear, let me know and I will do my best. My usual MO is to use Excel to join and lookup values to build tables from several .csv files. I have very adept at performing these actions, and while sometimes slow, it does the job. I have had it referred to as the tradesman way of doing things, and haven't got a great deal of experience with Access, SQL or relational databases, although I can waddle my way through. I appreciate the power and might of SAS, and would like to start performing these actions in EG for myself, with a view that other non SAS users can run these as a stored process or project as backup in a pinch. Ok, here is where I am at. Importing Data - all good Formats, Informats and Labels - all good Converting to SAS datasets - all good, I have converted my lookup files into SAS sets, as well as my source files. Calculated fields - ok, still working on it (missing values had me for a sec) Joining Tables - hmmmm not working the same way as I thought it would Example – Old Method join I concatenate three columns into one value in all tables, to get one index value or primary key. Starting the query the tbl_Source1, I use this value to vlook other tables to return values to separate columns. Let's call it Key (var1,var2,var3).I have simplified it down to two returned values and a few other calc fields and a value lookup. tbl_Source1 Key (var1,var2,var3) var1 var2 var3 var3_literal X Y Calc_Field Calc_Field2 redwhiteblue Red White Blue Turquoise 1 2 =(X+Y) =(X*20) tbl_Source2 key X redwhiteblue 1 tbl_Source3 key Y redwhiteblue 2 tbl_Lookup1 var3 var3_literal Blue Turquoise New Method (the way I see it) Using the query builder, I have tried to perform this however this seems to drop columns, and I can't see with clear visual inference when a value does not match to the other sets. Q2. What type of join would I require to perform this? I have tried several methods, and I don’t seem to be hitting the mark. I am performing all joins in the one query based on tbl_source1. I have had success with the calc fields using advanced expression. tbl_source1 (post query) var1 var2 var3 var3_literal X Y Calc_Field Calc_Field2 Red White Blue Turquoise 1 2 sum(X,Y) (X*20) tbl_Source2 var1 var2 var3 x Red White Blue 1 tbl_source3 var1 var2 var3 y Red White Blue 2 tbl_Lookup1 var3 var3_literal Blue Turquoise Apart from the join, I would like to see other results as well. If the three values (var1,var2,var3) is in tbl_source1, are not in tbl_source2 or tbl_source3, output a subset with these observations (non-matched records). If the three values (var1,var2,var3) is in tbl_source2 or tbl_source3 but not in tbl_source1, output two subsets with these observations. If var1, var2, var3 as separate and individual values appear don’t appear in all tables, create a method to identify them. And for the lookup join (similar to last) If var3 presents in any source table, but does not appear in tbl_Lookup1, create a subset of records, or identify values that are in the source files but does not appear in the lookup. A validation that the lookup contains all the values is required. Some of this was covered in Prog 1, however not in this detail, and not in EG. Any guidance would be appreciated, Michael ... View more