topic Re: Searching values in SAS Programming

Searching values

Vasundha — Fri, 23 Feb 2024 15:01:46 GMT

Hi All,
I’ve two tables named A & B. In A I’ve 2 columns named Complaint, ExactReason. In B Ive 3 columns named SubCategory, Issue, Reason.
For complaint column it got long string values and the ExactReason is an empty column. Now i want to create a code where it takes a value from SubCategory and searches it in Complaint Column, if there’s a match and then takes a value from Issue and searches again it in Complaint, again if there’s a match and then again takes a value from Reason and searches it in Complaint column. After finding a match with all these 3 column values in Complaint column then what it should do is, it should concatenate all these 3 values into one single sentence and puts that value in the ExactReason column with the corresponding matched value of the Complaint Column. Requesting you all to help me out. Thank you very much!.

Re: Searching values

ballardw — Fri, 23 Feb 2024 15:33:37 GMT

Example data and work through examples with actual values shown in your example data.

I am afraid that text like "and searches again it in Complaint, again if there’s a match and then again takes a value from Reason and searches it in Complaint column. After finding a match with all these 3 column values in Complaint column then what it should do is, it should concatenate all these 3 values into one single sentence and puts that value in the ExactReason " it is very hard to determine what may be needed with your exact values.

I am also not seeing clearly what is to be done if one one or two "matches" are found.

Note that you need to provide some rules and examples of the output. Since you are asking about multiple matches then ORDER of results might be an important bit and you need to provide rules for that as well.

Secondary not: almost any variable that holds two or more actual values is poor data design in general and likely should be reconsidered.

Re: Searching values

Vasundha — Fri, 23 Feb 2024 16:26:27 GMT

Thanks for responding!. Actually, I’ve only one column in B file which is Reason. For some reason, I split those values into 3 different columns to search each value separately in Complaint column.

Subcategory
Water filter and chilled water dispenser.
Issue
Frozen
Reason
Manifold.

I actually have all these words as one single sentence. What i want to achieve is. In table A column Complaint it’ll have Customer complaints. What we need to do is, I need to search this single sentence in customer complaint column. If this sentence matches with the customer complaint then we need to place this sentence in ExactReason column

Re: Searching values

PaigeMiller — Fri, 23 Feb 2024 18:01:56 GMT

The request was

Example data and work through examples with actual values shown in your example data.

I see only one of the two data sets. I don't see where you have provided examples worked through so we can see what output you want.

Re: Searching values

Vasundha — Sat, 24 Feb 2024 02:25:40 GMT

Here's the example!. I apologize for not providing detailed info also for late response.

I've just given only one example but there are two thousand records of different complaints.

Data A;
Input complaint $100. ExactReason $50.;
Cards;
DAMAGED / MISSING PART WATER LINE 
REPLACED BROKEN WATER LINE 
;
RUN;

DATA B;
REASON $50.;
CARDS;
DOORS & HANDLES : DOOR / DRAWER APPEARANCE / FINISH DEFECT : MISSING / WRONG PARTS 
;
RUN;


Output;
Complaint                                                                 ExactReason  
DAMAGED / MISSING PART WATER LINE 
REPLACED BROKEN WATER LINE                                                DOORS & HANDLES : DOOR / 
                                                                          DRAWER APPEARANCE / FINISH DEFECT : MISSING / WRONG PARTS

Now, what I want is the value in Reason column of table B should be searched in the Complaint column of table A. If there's a match keep the value in Reason column in ExactReason column.