Hello,
I'm trying to perform the following action to a dataset, where variable names are in paratheses:
- for ID number (ID) "2222", "2323" and "2425", if reference number (REFNO) includes "HLC", then the code variable (CODE) is set to "13"
- for ID number (ID) is equal to "2222" and where reference number doesn't include "HLC", the CODE is set to "99"
- for ID number (ID) "2323" and "2425", and where reference number doesn't include "HLC", then the code variable (CODE) is set to "98"
Although, REFNO isn't of equal lenght and HLC isn't necessarily always in the same "spot".
I came across the subtring, prx, scan functions but I'm not certain they would work or if there's an easier way to go about with this.
First question - is there a way to scan a string/character variable for a set of characters when the set of characters (HLC) doesn't isn't necessarily always at the same spot?
Second - I would essentially like to create a new binary variable where if the characters "HLC" are present, then binary variable = 1 and if HLC not present within REFNO, then binary variable = 0 and essentially use that binary variable when using the "IF, THEN, ELSE" statement. Any particular suggestions for other alternative ways to reach the same end result?
Thank you!
CF
The functions you are looking for could include INDEX, INDEXW, FIND, FINDW ... it all depends on what you mean when you say that REFNO "includes HLC". Would any of these be a match:
1234HLC81A
HLC25
GHLC
If so, you would need to use INDEX or FIND. If not, you would need to use INDEXW or FINDW. Basically, the "W" means look for words, rather than a series of characters. So you might use:
newvar = findw(refno, 'HLC', , 'i');
The "i" at the end indicates that case should be ignored, so the function can find HLC, Hlc, hlc, etc.
The new variable may be sufficient. It will contain 0 if the string is not found, or an integer greater than zero when the string is found. You can always convert the values greater than zero to 1 using IF/THEN statements. But you likely don't have to. For example, this statement treats "0" as false, and all other nonmissing values as true:
if newvar then do;
From that point, I think you just need to code a few IF/THEn statements.
The functions you are looking for could include INDEX, INDEXW, FIND, FINDW ... it all depends on what you mean when you say that REFNO "includes HLC". Would any of these be a match:
1234HLC81A
HLC25
GHLC
If so, you would need to use INDEX or FIND. If not, you would need to use INDEXW or FINDW. Basically, the "W" means look for words, rather than a series of characters. So you might use:
newvar = findw(refno, 'HLC', , 'i');
The "i" at the end indicates that case should be ignored, so the function can find HLC, Hlc, hlc, etc.
The new variable may be sufficient. It will contain 0 if the string is not found, or an integer greater than zero when the string is found. You can always convert the values greater than zero to 1 using IF/THEN statements. But you likely don't have to. For example, this statement treats "0" as false, and all other nonmissing values as true:
if newvar then do;
From that point, I think you just need to code a few IF/THEn statements.
Thank you Astounding!
I tried out your technique and it seems to work perfectly!
I did also use the following code below but I think that only assigns REFNO2=1 if the REFNO starts with HLC? (most of the reference number in my data sets do start with HLC but I would rather not take a chance and risk missing observations where the reference number contains 'HLC' but doesn't necessarily start with "HLC".
DATA out.step05;
set out.step0;
if REFNO=:'HLC' then REFNO2 = 1;
ELSE REFNO2 = 0;
RUN;
Thank you!
CF
Yes, you're correct about the results from =:
Many people think of =: as meaning "begins with". Technically, it's something very similar. It means that for comparison purposes, use the length of whichever string is shorter. So these two comparisons would return identical results:
if refno =: 'HLC' then refno2=1;
if 'HLC' =: refno then refno2=1;
Now it makes no sense to say "If 'HLC' begins with the value of REFNO ..." But that's not exactly what =: means. It really means that for purposes of making the comparison, shorten the longer string down to the length of the shorter string. Finally, note that the colon can be used with other comparison operators, not just an equal sign:
if 'A' <: lastname <: 'G';
This would get you all LASTNAME values that begin with "B" through "F".
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: