Hello,
I'm trying to identify whether components in one column match the components in another column.
For example,
Person ID | var1 | start date | Var2 | v2_start date | v2_end date |
1 | Dog+Pig | 01/18/2018 | dog | 04/12/2018 | 04/19/2018 |
1 | Dog+Pig | 01/18/2018 | pig | 07/25/2018 | 08/22/2018 |
1 | Dog+Pig | 01/18/2018 | lion | 10/04/2018 | 11/15/2018 |
From the table, I want to set some conditions to identify the dates and var2. The conditions are
1) "var2" does NOT contain any words or components of "var1" or that there is no match in any of the components in both variables.
2) The start date for "var2" is 30 days after the start date for "var1".
Then, the table I want is,
Person ID | var1 | v1_start date | WANT | v2_start date | v2_end date |
1 | Dog+Pig | 01/18/2018 | lion | 10/04/2018 | 11/15/2018 |
The method I tried so far is to mark whether to keep or remove based on the conditions I set.
PROC SQL;
CREATE TABLE want AS
SELECT personid, var1, v1_startdate, var2, v2_startdate,
(Case when
(v2_startdate > v1_startdate+30) & (var2 <> va1)) THEN 1 ELSE 0 END) AS KEEEP
FROM have
GROUP BY personid;
QUIT;
Please let me know if you need any clarification!
How about this?
data want;
set have;
if (v2_startdate > v1_startdate+30) and (find(var1,var2,'it')=0);
run;
How about this?
data want;
set have;
if (v2_startdate > v1_startdate+30) and (find(var1,var2,'it')=0);
run;
It worked! I really appreciate your help.
Just so that I have a good understanding of what happened, what does 'it' do when it's in the function of FIND()? I never thought about trying out this way nor seen other responses.
"i or I" ignores character case during the search.
If this modifier is not specified, FIND cannot searches "dog" in "Dog".
find(var1,var2,'i')
is same like
find(upcase(var1),upcase(var2))
"t or T" trims trailing blanks from string and substring.
If var1 or var2 has a long variable length, such as $100, it must be trimmed to include trailing blanks in the search.
find(var1,var2,'t')
is same as
find(trim(var1),trim(var2))
https://documentation.sas.com/doc/en/pgmcdc/8.11/proccas/n0kcfmf5agtudun1v1zkvnbd7970.htm
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.