topic Re: How do I identify that components of one column does not match the components in another column? in SAS Programming

How do I identify that components of one column does not match the components in another column?

jsyang56 — Wed, 13 Apr 2022 01:25:52 GMT

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!

Re: How do I identify that components of one column does not match the components in another column?

japelin — Wed, 13 Apr 2022 01:45:24 GMT

How about this?

data want;
  set have;
  if (v2_startdate > v1_startdate+30) and (find(var1,var2,'it')=0);
run;

Re: How do I identify that components of one column does not match the components in another column?

jsyang56 — Wed, 13 Apr 2022 01:56:50 GMT

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.

Re: How do I identify that components of one column does not match the components in another column?

japelin — Wed, 13 Apr 2022 02:40:36 GMT

"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