01-24-2013 05:16 AM
I have two columns. One containing a vehicle make and the other a vehicle model. For example:
In some instances (from poor validation at data entry most probably), Column2 often contains the value from Column1 within the string. Example:
VOLVO VOLVO - LR55VL
I was hoping to use the COMPRESS function like
COMPRESS(Column2, Column1) but that looks at the individual characters in column1 and compresses them ALL out of Column2 resulting in
VOLVO - R55
How can I do something similar to compress but force it to contstrain the compression with the entire string in column1?
In this example, i was hoping to achieve and output of
VOLVO - LR55VL
Thanks in advance!
01-24-2013 06:44 AM
01-24-2013 05:33 AM
Considering that the column model has model value associated with the '-' to make, then you can follow the below code to get the desired result. Please let me know if this works for you
input make : $10. model :$20.;
if upcase(make)=upcase(scan(model,1,'-')) then
01-24-2013 06:07 AM
Thanks for such a quick response. Wasn't aware of the scan function and that would work for the example i provided. However i should have probably provided a little more of an example to show the range of the problem.
In that, the make is not always followed by a "-". There isn't really any consistency as to where the make name could appear in the model field. Another example could be
This list isn't exhastive as some of the names are input by humans by free text
So i'm guessing i need to put a CONTAINS element in here. In logic would be something like
Assess if string in column2 CONTAINS string in column1 and provide the start postition of where the string in column1 is in column2. Then delete the following LEN(column1) from that start position.
Does that make sense?
01-24-2013 06:16 AM
CONTAINS are only valid in where clauses, but you could use findw (not scan - sorry).
The rest sounds good...
01-24-2013 06:44 AM
11-18-2013 01:51 PM
Another approach, using Prx functions:
input make:$10. model&$20.;
model = transtrn(model,strip(make),strip(' '));
VOLVO LR55 VOLVO
VOLVO VOLVO LR55
01-24-2013 06:05 AM
By using the scan, length and substring functions, you can romove your word from anywhere in the other column.
11-18-2013 10:50 AM
So, to be clear, it is not possible to use compress() to remove a word/phrase/string? It will also remove the component parts (e.g., letters) entirely?
11-18-2013 02:46 PM
But, as you asked if "it was not possible", you have opened up a can of worms. If the word(s) are comprised of specific characters, and those characters don't exist anywhere else in the string, then it is definitely possible. e.g., using compress(string,'a') will definitely remove the "word" from strings like:
This is a nice evening
Is it a boy or girl?
Need further help from the community? Please ask a new question.