Dear SAS community,   I have two source tables which I'd like to combine into one dataset. I'd like to perform a check based on some conditions which'll decide whether I select the records from Table A or the records Table B. The conditions will be based on the maximum depth for a group across the two tables and the number of records within the group in events where the maximum depth is equal across the two tables. With everything being equal, I prefer to use the records from Table A.   For demonstration purposes, let's say I have these tables:   Table A:                                 Name   Depth  Coord_X   Coord_Y A          0           5               6                         A          100       5               6                      A          200       5               6                          A          300       5               6       B          0           4               3                          B          2000     4               3 B          3000     4               3                           B          4000     4               3                          C          0           2               1                          C          3000     2               1                         C          4000     2               1                         C          5000     2               1          Table B Name   Depth  Coord_X   Coord_Y A          0           5               6                                          A          250       5               6                          A          300       5               6       B          0           4               3                          B          2000     4               3  B          3000     4               3                         B          4000     4               3                          C          0           2               1                          C          3000     2               1                         C          4000     2               1                         C          5000     2               1 C          5500     2               1 D          0           11             12 D          10         11             12 D          20         11             12    As can be seen, the tables contains the same attributes. However, Table A may contain some groups not in Table B, and vice versa. This can be seen in the example above where Table B contains a group D not in Table A.   First, I'd like to test on the maximum depth - I want to select the records from the input table which contains the greatest depth. Using the tables above as an example, for group C, I'd like my target table to contain the records from Table B as this has a greater maximum depth then Table A. The maximum depth is the key condition, and as long as a table contains a greater maximum depth then the other table, the number of records within each group is irrelevant, and I always select the records from the source containing the maximum depth. In other words, even if Table A contained 1000 records and Table B contained 4 records, as long as Table B contains a greater maximum depth I select the 4 records from Table B over the 1000 records from Table A.   Secondly, if the two tables contains an equal maximum depth for a group, I use the number of records within each group as a tie breaker. Using the above tables as an example, for group A, I want to select the records from Table A as this contains 4 records compared to the 3 records in Table B with the maximum depth being equal between the two tables.   In the event that both the maximum depth and the number of records are equal, I want to use Table A as my source.   Overall, based on these conditions, my target table should look like this. Notice that I want to add a column which tells which source has been selected for my Target table:   Target Table Name   Depth  Coord_X   Coord_Y   Source A          0           5               6                 Table A A          100       5               6                 Table A A          200       5               6                 Table A         A          300       5               6                 Table A B          0           4               3                 Table A         B          2000     4               3                 Table A B          3000     4               3                 Table A        B          4000     4               3                 Table A        C          0           2               1                 Table B         C          3000     2               1                 Table B        C          4000     2               1                 Table B        C          5000     2               1                 Table B C          5500     2               1                 Table B D          0           11             12               Table B D          10         11             12               Table B D          20         11             12               Table B   I am fairly new to SAS and programming in general, and I am therefore unsure what the best strategy to solving this issue efficiently would be.  ... View more

Hey SAS Community,   Asked a question yesterday, but wanted to create a new post which contains more of the specifics I am working with.   I have two input tables:   The first table (denoted Have1) contains the columns CodeName and CodeKeywords in the following format:   CodeName                                        CodeKeywords AIS  TEST                                          ais-test | pressure-drop FIELD INSTRUMENT ERROR          psd GVL TEST                                         GVL drop LIFT ISSUE                                       lift | slackline SOLID LOADING                              pbu PRODUCTION BOTTLENECK         resd    These keywords are entered by engineers in an application and are retrieved by a PROC HTTP call, as such I cannot guarantee the uniqueness of the keywords. As such, I am happy for the first or last match to be the assigned Code in the event of multiple matches. Moreover, the keywords are set up for use with prxmatch such that a keyword like "ais-test | pressure-drop" would match comments containing either "ais-test" OR "pressure-drop".   The second table (denoted Have2) contains the columns Date and Comment - where the comment is text written by an engineer and can be missing: Date                         Daily Comment 27MAR2021             Encountered issues with PSD 27MAR2021               27MAR2021             Had issues with the lift system, no actions taken 27MAR2021             Experienced pressure-drop 27MAR2021             RESD during morning routine 27MAR2021             No issues encountered   So from the above tables, my desired output would be as follows:   Date                          Daily Comment                                                             CodeSugg 27MAR2021              Encountered issues with PSD                                        FIELD INSTRUMENT ERROR   27MAR2021               27MAR2021              Had issues with the lift system, no actions taken           LIFT ISSUE   27MAR2021              Experienced pressure-drop                                            AIS  TEST  27MAR2021              RESD during morning routine                                        PRODUCTION BOTTLENECK 27MAR2021              No issues encountered   As illustrated by the above tables, I use the keywords to search the comments for a match - for instance, when finding the word RESD in the "RESD during morning routine" comment the comment should be labelled by the Production Bottleneck code. Until now, I've used prxmatch for this purpose. Additionally, I'd like the resulting Want table to have the same number of rows as the Have2 table (i.e., all comments present in resulting Want table). Note also that if there is no match, no Label should be added in the CodeSugg column for the corresponding comment value.   Previously I hardcoded this and it was working satisfactorily:       data want; set have; %if %symexist(ASV_FAILURE) %then %do; if prxmacth("/&ASV_FAILURE./i", loss_comment) then CODE_SUGG = "AVS Failure"; %end; run;       I am now trying to create a more "dynamic" code to facilitate for new unknown codes which may be added in the future. For this purpose I've created the following script:       proc sql noprint; select count(*) into :rowCountHave1 from Have1; quit; %macro suggestCode(); data want; %let iter = 1; set Have2; %let comment=DAILY_COMMENT; %do %while (&iter. <= &rowCountHave1.); set Have1(firstobs=&iter. obs=&iter.); %let keyword=strip(CodeKeywords); %let code=strip(CodeName); %if prxmatch("/%str(&keyword.)/i", &comment.) %then CODESUGG=&lcode.;; %let iter=%eval(&iter.+1); %end; run; %mend; %suggestCode();       The rationale behind the above code was that for each comment I would iterate through the table containing the codes and keywords and check for a match. Disclaimer - I have very little experience with looping in SAS and SAS programming in general and this code is currently not working as expected and I encounter the following error without really understanding why:   - ERROR: Required operator not found in expression: prxmatch("/&keyword./i.", &comment.) - ERROR: The macro SUGGESTCODE will stop executing   I figured the easiest solution for my task was to use a do-while loop for each row in the table (Have1) containing codes and keywords and check for a match for each comment in the Have2 table. Would greatly appreciate any suggestions and comments on how far I am off to get this approach to work.   ... View more

Dear community,   Fairly new to SAS and am currently stuck on something which there is hopefully a simple solution for - a solution which I have not been able to find thus far unfortunately.   My issue is that I want to search for a defined list of special characters (say "-,.|''_!") and replace any occurence of these with another character (say "|").    For instance, for the string "My, name - is. Lars!" I would like to produce the string "My|name|is|Lars|" (silly example but hopefully it illustrates my desired output) if I specified the characters '-,.!' to be replaced and '|' to be the replacement. In pseudocode it would likely look something like this:   %let chars_to_replace = '-,+*!.=^!'; *Gives a list of special characters to search for and replace %let char_to_replace_with = '|'; *Gives the character I want to replace with data &_output; set &_input; new_string = magical_function(old_string, &chars_to_replace, &char_to_replace_with); run; As of yet, I have not been able to find the correct function to perform this operation, but I am hopeful such a function exists within SAS.   ... View more