topic Re: deriving variable by looking between observations in SAS Programming

deriving variable by looking between observations

sasg — Tue, 17 Oct 2017 22:56:03 GMT

Hi All,

Here is the sample data. I need to derive two variables llt_code and comments. Below is the rules for derivation.

Year Study Term LLT

2000 123 nausea A

2000 456 nausea B

2001 789 nausea C

2001 789 nausea A

If TERM is same but has different values in LLT then populate LLT_CODE = “Codes do not match” for all these records. Or, set LLT_CODE to “Match”.

Comment: Concatenate unique different LLT_TERM within same MHTERM with ‘; ‘ delimeter.

Ex: LLT_CODE =’ Codes do not match’ and comment =’Comparing with A; B; C’

Final Data:

Year Study Term LLT LLT_TERM comments

2000 123 nausea A Codes do not match Comparing with A; B; C

2000 456 nausea B Codes do not match Comparing with A; B; C

2001 789 nausea C Codes do not match Comparing with A; B; C

2001 789 nausea A Codes do not match Comparing with A; B; C

Could someone please help me with the logic.

Thanks a Lot.

Re: deriving variable by looking between observations

PGStats — Wed, 18 Oct 2017 00:21:14 GMT

All your example cases are the same. Please show a more complete example with some matches. Tell us what happens when some records match and others don't.

Re: deriving variable by looking between observations

sasg — Wed, 18 Oct 2017 00:40:56 GMT

Year    Study    Term             LLT
2000   123         nausea          A
2000   456         nausea          B
2001    789         nausea         C
2001    442         nausea         A
2000    234      headache       ache
2001 423     headache       ache
2000    123         toothpain      pain
2000    234      toothpain       ache
2001 423         toothpain       ache

output should look like this:

Final Data: based on the term we need to apply the logic.

Year    Study    Term             LLT LLT_TERM                             comments
2000   123         nausea          A     Codes do not match                Comparing with A; B; C
2000   456         nausea          B     Codes do not match                 Comparing with A; B; C
2001    789         nausea         C     Codes do not match                  Comparing with A; B; C
2001    442         nausea         A     Codes do not match                  Comparing with A; B; C

2000    234      headache       ache   match
2001 423     headache       ache   match
2000    123         toothpain      pain   Codes do not match   Comparing with pain; ache
2000    234      toothpain       ache Codes do not match   Comparing with pain; ache
2001 423         toothpain       ache Codes do not match   Comparing with pain; ache

Re: deriving variable by looking between observations

error_prone — Wed, 18 Oct 2017 02:15:21 GMT

Problem can be solved by using "look ahead and back"-technique. You will find papers and posts explaining it in detail.

Re: deriving variable by looking between observations

Ksharp — Wed, 18 Oct 2017 13:11:43 GMT

Assuming I understand what you mean.

data have;
input Year    Study    Term  : $20.           LLT $;
cards;
2000   123         nausea          A
2000   456         nausea          B
2001    789         nausea         C
2001    442         nausea         A
2000    234      headache       ache
2001 423     headache       ache
2000    123         toothpain      pain
2000    234      toothpain       ache
2001 423         toothpain       ache
;
run;
data want;
length list $ 20000  LLT_TERM comments $ 80;
n=0;
 do until(last.term);
  set have;
  by term notsorted;
  n+1;
  if not findw(list,strip(llt),'; ') then list=catx(';',list,llt);
 end;
 flag=countw(list,';');
 do until(last.term);
  set have;
  by term notsorted;  
  if flag=1 then do;comments=' '; LLT_TERM='matched'; end;
   else do;comments='Comparing with '||list; LLT_TERM=' Codes do not match';end;
  output;
 end;
drop n flag list;
 run;

Re: deriving variable by looking between observations

PGStats — Wed, 18 Oct 2017 17:49:05 GMT

Great code @Ksharp! Could be improved by putting the assignments to comments and LLT_TERM before the second do until loop. You could also get rid of flag and n.

Re: deriving variable by looking between observations

Ksharp — Thu, 19 Oct 2017 11:55:40 GMT

@PGStats , Yes. As you wish . Glad to see you come back .

Re: deriving variable by looking between observations

sasg — Thu, 19 Oct 2017 19:44:14 GMT

Thank You Very Much. Working as expected.

Re: deriving variable by looking between observations

sasg — Tue, 31 Oct 2017 14:47:56 GMT

Hi ,

have issue with the code for below scenarios, Where llt is missing for 2000 or 2001. It is showing as count 1 so assigning as match,but this scenario should be treated as codes do not match.

2000 123 nausea A
2001 442 nausea .

have another scenario: where in 2001 same term but llt value is different for 2001 records, in this scenario also getting match, but it should come as do not match.

2000 123 nausea A

2000 234 nausea A
2001 442 nausea A

2001 442 nausea .

Re: deriving variable by looking between observations

sasg — Wed, 01 Nov 2017 02:13:42 GMT

Hi This solution is working for all other scenarios, but facing issues in the below scenarios. Could you please suggest how to fix this issue.

study year term llt

123 2001 headache pain

234 2001 headache pain

123 2002 headache pain

456 2002 headache .

234 2001 neckpain injury

456 2002 neckpain .

In the above scenarios flag as 1, because there is no other code in this scenario I want to display codes does not match but it is showing as match. Please suggest me the fix for this scenario.

Thanks,

sasg

Re: deriving variable by looking between observations

Ksharp — Wed, 01 Nov 2017 13:10:55 GMT

OK. No problem. Just assign a  value to missing value.
Note: the value stand for missing value can't be the same as LLT, here I use 'NA'.


data have;
input Year    Study    Term  : $20.           LLT $;
if missing(LLT) then LLT='NA';
cards;
2000   123         nausea          A
2000   456         nausea          B
2001    789         nausea         C
2001    442         nausea         A
2000    234      headache       ache
2001 423     headache       ache
2000    123         toothpain      pain
2000    234      toothpain       ache
2001 423         toothpain       ache
2001   23       headache      pain
2001   234       headache       pain
2002   123       headache       pain
2002   456        headache       .
;
run;
data want;
length list $ 2000  LLT_TERM comments $ 80;
n=0;
 do until(last.term);
  set have;
  by term notsorted;
  n+1;
  if not findw(list,strip(llt),'; ') then list=catx(';',list,llt);
 end;
 flag=countw(list,';');
 do until(last.term);
  set have;
  by term notsorted;  
  if flag=1 then do;comments=' '; LLT_TERM='matched'; end;
   else do;comments='Comparing with '||list; LLT_TERM=' Codes do not match';end;
  output;
 end;
drop n flag list;
 run;

Re: deriving variable by looking between observations

sasg — Wed, 01 Nov 2017 14:14:37 GMT

Thank a Lot.

Re: deriving variable by looking between observations

sasg — Thu, 02 Nov 2017 18:18:37 GMT

one more last question if I want to display LLT_CODE in the comments as alphabetical order.

ex:

2000 123 nausea vomiting
2001 456 nausea nausea

2000 123 headache injury
2001 456 headache pain

comments should be 'Comparing nausea ; vomiting'

'Comparing injury; pain'

Re: deriving variable by looking between observations

Ksharp — Fri, 03 Nov 2017 12:47:32 GMT

OK. No problem.


data have;
input Year    Study    Term  : $20.           LLT $;
if missing(LLT) then LLT='NA';
cards;
2000   123         nausea          C
2000   456         nausea          B
2001    789         nausea         A
2001    442         nausea         A
2000    234      headache       ache
2001 423     headache       ache
2000    123         toothpain      pain
2000    234      toothpain       ache
2001 423         toothpain       ache
2001   23       headache      pain
2001   234       headache       pain
2002   123       headache       pain
2002   456        headache       .
;
run;
data want;
length list $ 2000  LLT_TERM comments $ 80;
n=0;
 do until(last.term);
  set have;
  by term notsorted;
  n+1;
  if not findw(list,strip(llt),'; ') then list=catx(';',list,llt);
 end;
 flag=countw(list,';');
 do until(last.term);
  set have;
  by term notsorted;  
  if flag=1 then do;comments=' '; LLT_TERM='matched'; end;
   else do;comments=list; LLT_TERM=' Codes do not match';end;
  output;
 end;
drop n flag list;
 run;



 data final_want;
  set want;
  length new_comments $ 2000;
  array x{999} $ 40;
  do i=1 to countw(comments,';');
    x{i}=scan(comments,i,';');
	if x{i}='NA' then x{i}=' ';
  end;
 call sortc(of x{*});
 new_comments='Comparing with: '||catx(';',of x{*});
 drop comments x1-x999 i;
run;