topic Finding a match of values of our interest if it it is singly present or combination i need to flag in SAS Programming

Finding a match of values of our interest if it it is singly present or combination i need to flag

carl_miles — Wed, 06 Sep 2023 04:49:21 GMT

Consider the dataset

Data Exam;

format date date9.;
Input ID $ exam_date Test1 $;
Datalines;
9000001 15jan A
9000001 16jan A+B
9000001 17jan A+C

9000001 15jan A
9000001 16jan D
9000001 17jan A+E

my answer should be

if it contains A, B Or in combination then 1 if they have something other with them then 0

9000001 15jan A ---------- 1
9000001 16jan A+B ------- 1
9000001 17jan A+C ------- 0

9000001 15jan A ---------- 1
9000001 16jan D ---------- 0
9000001 17jan A+E ------- 0

Can you write a code which can help me in this ode this flag
here we have only A,B two values max to max four combination i will get (A,B,A+B,B+A)
But I want code that will handle as many as values

Re: Finding a match of values of our interest if it it is singly present or combination i need to fl

Oligolas — Wed, 06 Sep 2023 05:06:52 GMT

Hi,

here you go:

data want;
   set exam;
   length flag 8;
   flag=ifn(verify(compress(test1,,'ak'),'AB'),0,1);
run;

Re: Finding a match of values of our interest if it it is singly present or combination i need to fl

carl_miles — Wed, 06 Sep 2023 05:42:55 GMT

Thank you so much

Could you please explain how this step is working
flag=ifn(verify(compress(test1,,'ak'),'AB'),0,1);

Re: Finding a match of values of our interest if it it is singly present or combination i need to fl

carl_miles — Wed, 06 Sep 2023 05:43:25 GMT

and why we are declaring ak

Re: Finding a match of values of our interest if it it is singly present or combination i need to fl

Oligolas — Wed, 06 Sep 2023 07:09:04 GMT

flag=ifn(verify(compress(test1,,'ak'),'AB'),0,1);

in SAS double click on a function and press F1 to start the help on the corresponding function page.

Start with the innermost function which is compress.

compress is used to remove signs.

The modifier 'a' means to select the alphabetic characters to the list of characters (to remove)

but the following 'k' modifier means to keep the 'keep the characters in the list instead of removing them'

so 'ak' means delete anything but keep the alphatic characters from test1.

Verify 'Returns the position of the first character in a string that is not in any of several other strings'

so it returns a numeric>0 if another character is found than the ones specified

Verify AB returns a numeric>0 if the result of compress contains anything else than A or B

The ifn function 'Returns a numeric value based on whether an expression is true, false, or missing.'

So if the Verify result is>0 than anything else than A or B was found so the flag should be 0

in the other case the flag should be 1

Re: Finding a match of values of our interest if it it is singly present or combination i need to fl

carl_miles — Wed, 06 Sep 2023 10:00:23 GMT

Thank you so much for the teaching

Actually I have case that

900001 sebastian

900001 east

900001 nested

900001 test

by following your code:

flag=ifn(verify(compress(test1,,'ak'),'sebastian', 'nested'),0,1);

I am getting flag

900001 sebastian ----------1

900001 east ----------------1

900001 nested -------------1

900001 test ----------------1

I want this case should be

900001 sebastian ----------1

900001 east ----------------0

900001 nested -------------1

900001 test ----------------0

Could you kindly help me with this

Re: Finding a match of values of our interest if it it is singly present or combination i need to fl

Oligolas — Wed, 06 Sep 2023 11:56:05 GMT

verify checks the presence of a character not if a word is matched, so if you specify 'sebastian' it will check for an 's', an 'e', a 'b' and so on...

if you want to match words you could use the findw function or use a regular expression

Data Exam;
Input ID $ Test1 $20.;
Datalines;
9000001 Sebastian
9000001 East
9000001 Nested
9000001 Test
;
run;

*case sensitive approach == no match;
data want1;
   set exam;
   length flag1 flag2 8;
   flag1=ifn(findw(test1,'sebastian') OR findw(test1,'nested'),1,0);
   flag2=prxmatch('/\bsebastian\b/',strip(test1));*with regular expression;
run;

*case insensitive approach;
data want2;
   set exam;
   length flag1 flag2 8;
   flag1=ifn(findw(test1,'sebastian', ' ','i') OR findw(test1,'nested', ' ','i'),1,0);
   flag2=prxmatch('/\bsebastian\b|\bnested\b/i',strip(test1));*with regular expression;
run;

Re: Finding a match of values of our interest if it it is singly present or combination i need to fl

carl_miles — Wed, 06 Sep 2023 13:13:56 GMT

Data Exam;
Input ID $ Test1 $40.;
Datalines;
9000001 Sebastian
9000001 East
9000001 Nested, test
9000001 Test
9000001 Sebastian,Test
9000001 Sebastian,Nested
;
run;

*case sensitive approach == no match;
data want1;
   set exam;
   length flag1 flag2 8;
   flag1=ifn(findw(test1,'sebastian') OR findw(test1,'nested'),1,0);
   flag2=prxmatch('/\bsebastian\b/',strip(test1));*with regular expression;
run;

*case insensitive approach;
data want2;
   set exam;
   length flag1 flag2 8;
   flag1=ifn(findw(test1,'sebastian', ' ','i') OR findw(test1,'nested', ' ','i'),1,0);
   flag2=prxmatch('/\bsebastian\b|\bnested\b/i',strip(test1));*with regular expression;
run;

The code is really helpful

Its just I should have mentioned this scenerio earlier
Right now I am getting something in the attached photo

So where i highlighted where it should be 0 but I getting 1

Could you help me like how can we approach this problem where i need the word in combination as well if it is present as single or multiple in combination

So I assumed the same logic instead A, B. C i used words
Could give help me to get the code like in words

Re: Finding a match of values of our interest if it it is singly present or combination i need to fl

Oligolas — Thu, 07 Sep 2023 10:08:29 GMT

Hi,

I see, in this case I'd loop over the words to check the match


*case insensitive approach;
data want2;
   set exam;
   length flag1 flag2 8 CurrentWord $200;

   do i=1 to countw(test1); *loop over the words consult the help to check which delimiters are used;
      CurrentWord=scan(test1,i);
      flag1=ifn(findw(CurrentWord,'sebastian', ' ','i') OR findw(CurrentWord,'nested', ' ','i'),1,0);
      flag2=prxmatch('/^\bsebastian\b|\bnested\b$/i',strip(CurrentWord)); *with regular expression;
      if flag1 ne flag2 then put 'ERROR: Please check inconsistency ' test1= CurrentWord=;
      if flag1 eq 0 then leave; *stop loop in case a word is not matched and return 0;
   end;
   drop CurrentWord i;
run;

Re: Finding a match of values of our interest if it it is singly present or combination i need to fl

carl_miles — Wed, 20 Sep 2023 10:06:22 GMT

Hi Oligolas,

data CustomerOrders2;
input CustomerID2 OrderDate2 :date9.;
format OrderDate2 date9.;
datalines;
1001 01JAN2023
1001 15FEB2023
1001 12DEC2023
1001 25JAN2024
;
run;
So in above code we can see that Orderdate2

I want one more column that will be next date following will come as below mentioned output i want
CustomerID2 OrderDate2 Orderdateprev

1001 01JAN2023 15FEB2023

1001 15FEB2023 12DEC2023

1001 12DEC2023 25JAN2024

1001 25JAN2024 -

So the next immediate will append and if there is none then it will be null but it will still in cohort

Could you guide me here

Re: Finding a match of values of our interest if it it is singly present or combination i need to fl

Oligolas — Wed, 20 Sep 2023 13:01:47 GMT

Here you go: lag function

data CustomerOrders2;
input CustomerID2 OrderDate2 :date9.;
format OrderDate2 date9.;
datalines;
1001 01JAN2023
1001 15FEB2023
1001 12DEC2023
1001 25JAN2024
;
run;

proc sort; by CustomerID2 descending OrderDate2 ; run;

data want;
   set CustomerOrders2;
   by CustomerID2 descending OrderDate2;
   length NextDate 8;
   format NextDate date9.;
   NextDate=lag1(OrderDate2);
   if first.CustomerID2 then NextDate=.;
run;

proc sort; by CustomerID2 OrderDate2 ; run;

Re: Finding a match of values of our interest if it it is singly present or combination i need to fl

carl_miles — Thu, 21 Sep 2023 10:38:22 GMT

Hi Oligolas,

thanks for the reply earlier
I have an dataset like this

cust_id date1 date2 date3

1001 15JAN2023 20FEB2023 30MAR2023

I want an ouput like

cust_id date1 date2 date3 date 4

1001 15JAN2023 20FEB2023 30MAR2023 15JAN2023

1001 15JAN2023 20FEB2023 30MAR2023 20FEB2023

if you see date1 and date2 are appended against date3

like that i want an output
i have dataset of 15000 rows
kindly guide me here pls

Re: Finding a match of values of our interest if it it is singly present or combination i need to fl

Oligolas — Thu, 21 Sep 2023 14:21:07 GMT

Difficult to say without any explanation of the derivation of Date4

Anyway I do not see the relation of your last questions with the topic of this thread and if there is, why you don't follow the guidance as explained before

Kindly ask you create a new topic to treat this new question pls