topic Re: Keeping an unscheduled visit from one dataset in a Hash Object merge in SAS Programming

Keeping an unscheduled visit from one dataset in a Hash Object merge

smackerz1988 — Wed, 26 Oct 2022 12:14:13 GMT

Hello,

I'm trying to keep an unscheduled visit that is only in one dataset for an aval derivation but I'm using a hash object merge. Here is a snippet of my data and dersired output.

data have1;
input  SUBJID $ PARAMCD $ ADT :$9. AVISITN :8. AVISIT :$12. AVAL :8. ;
infile datalines dlm = '|';
datalines;
1004|EASITOTA|21JUL2022|2|Day 1        |16.3
1004|EASITOTA|22JUL2022|99|Unscheduled |18
1004|EASITOTA|03AUG2022|4|Week 2   |8.6
1004|EASITOTA|17AUG2022|5|Week 4   |8.7
1004|EASITOTA|14SEP2022|6|Week 8   |9.6
;
run;

data have2;
input  SUBJID $  ADT :$9. AVISITN :8. AVISIT :$12. RTT1L28D :8. RTT1G28D :8. SWREL :8.  SWNREL :8. T2CM :8. RTT1L14D :8. RTT1G14D :8. NCUNODEV :8.; 
infile datalines dlm = '|';
datalines;
1004|21JUL2022|20|Day 1	|0|0|0|0|0|0|0|1
1004|03AUG2022|40|Week 2|0|0|0|0|0|0|0|1
1004|17AUG2022|50|Week 4|0|0|0|0|0|0|0|1
1004|14SEP2022|60|Week 8|0|0|0|0|0|0|0|1
1004|13OCT2022|70|Week 12|0|0|0|0|0|0|0|1
1004|10NOV2022|80|Week 16|0|0|0|0|0|0|0|1
;
run;

data want; input SUBJID $ PARAMCD $ ADT :$9. AVISITN :8. AVISIT :$12. AVAL :8. RTT1L28D :8. RTT1G28D :8. SWREL :8. SWNREL :8. T2CM :8. RTT1L14D :8. RTT1G14D :8. NCUNODEV :8.; infile datalines dlm = '|'; datalines; 1004|EASITOTA|21JUL2022|20|Day 1 |16.3|0|0|0|0|0|0|0|1 1004|EASITOTA|22JUL2022|99|Unscheduled |18 | | | | | | | | 1004|EASITOTA|03AUG2022|40|Week 2 |8.6 |0|0|0|0|0|0|0|1 1004|EASITOTA|17AUG2022|50|Week 4 |8.7 |0|0|0|0|0|0|0|1 1004|EASITOTA|14SEP2022|60|Week 8 |9.6 |0|0|0|0|0|0|0|1 1004|EASITOTA| |70|Week 12 | |0|0|0|0|0|0|0|1 1004|EASITOTA| |80|Week 16 | |0|0|0|0|0|0|0|1 ; run;

Re: Keeping an unscheduled visit from one dataset in a Hash Object merge

PeterClemmensen — Wed, 26 Oct 2022 10:27:44 GMT

Turn the lookup logic around. Read have1 in the Set Statement and look up from have2.

Like this

data have1;
input  SUBJID $ PARAMCD $ ADT :$9. AVISITN :8. AVISIT :$12.;
infile datalines dlm = '|';
datalines;
1004|EASITOTA|21JUL2022|20|Day 1      |
1004|EASITOTA|22JUL2022|99|Unscheduled|
1004|EASITOTA|03AUG2022|30|Week 2     |
1004|EASITOTA|17AUG2022|40|Week 4     |
1004|EASITOTA|14SEP2022|50|Week 8     |
;
run;

data have2;
input  SUBJID $  ADT :$9. AVISITN :8. AVISIT :$12. RTT1L28D :8. RTT1G28D :8. SWREL :8.  SWNREL :8. T2CM :8. RTT1L14D :8. RTT1G14D :8. NCUNODEV :8.; 
infile datalines dlm = '|';
datalines;
1004|21JUL2022|20|Day 1	 |0|0|0|0|0|0|0|1
1004|03AUG2022|30|Week 2 |0|0|0|0|0|0|0|1
1004|17AUG2022|40|Week 4 |0|0|0|0|0|0|0|1
1004|14SEP2022|50|Week 8 |0|0|0|0|0|0|0|1
;
run;

data want(drop=rc);
length subjid $40 avisit $200;
   if _N_ = 1 then do;
      dcl hash h(dataset : 'have2');
      h.definekey('SUBJID','AVISITN');
      h.definedata(all : 'Y');
      h.definedone();
   end;

   set have1;      
   if 0 then set have2; 

   call missing(of RTT1L28D -- NCUNODEV);

   rc = h.find();

run;

Re: Keeping an unscheduled visit from one dataset in a Hash Object merge

smackerz1988 — Wed, 26 Oct 2022 10:41:41 GMT

That works on the snippet dataset but not the actual dataset although the actual dataset have more column variables. Is it the case of increasing the call missing to account for them?

Re: Keeping an unscheduled visit from one dataset in a Hash Object merge

PeterClemmensen — Wed, 26 Oct 2022 10:43:12 GMT

It probably is. But I can't see your actual data, so I don't know 🙂

Re: Keeping an unscheduled visit from one dataset in a Hash Object merge

smackerz1988 — Wed, 26 Oct 2022 11:45:35 GMT

Yes I can't reverse the lookup logic. I need to keep it as is but somehow keep that Unscheduled visit too. Maybe hash object might be more trouble than it is worth here.

Re: Keeping an unscheduled visit from one dataset in a Hash Object merge

PeterClemmensen — Wed, 26 Oct 2022 11:46:19 GMT

Why can't you reverse the lookup logic?

Re: Keeping an unscheduled visit from one dataset in a Hash Object merge

smackerz1988 — Wed, 26 Oct 2022 11:50:15 GMT

it cause issues with the spec requirements. I need to retain all the visits from have2 but also an unscheduled visit with a baseline value of 'Y' from have1 essentially. If I reverse the logic visits from have2 that are not present in have1 are omitted. Sorry I know that is additional details not covered in the datasets provided but thought the solution would of been quicker with a simpler structure as it's a complicated dataset and spec.

Re: Keeping an unscheduled visit from one dataset in a Hash Object merge

PeterClemmensen — Wed, 26 Oct 2022 11:56:54 GMT

No problem. Let's take a step back.

You have 2 datasets have1 and have2. You want all the obs from have2 and only some obs from have1. This is not a lookup thing. This can be done by simply doing

data have1;
input  SUBJID $ PARAMCD $ ADT :$9. AVISITN :8. AVISIT :$12. ;
infile datalines dlm = '|';
datalines;
1004|EASITOTA|21JUL2022|20|Day 1      |
1004|EASITOTA|22JUL2022|99|Unscheduled|
1004|EASITOTA|03AUG2022|30|Week 2     |
1004|EASITOTA|17AUG2022|40|Week 4     |
1004|EASITOTA|14SEP2022|50|Week 8     |
;
run;

data have2;
input  SUBJID $  ADT :$9. AVISITN :8. AVISIT :$12. RTT1L28D :8. RTT1G28D :8. SWREL :8.  SWNREL :8. T2CM :8. RTT1L14D :8. RTT1G14D :8. NCUNODEV :8.; 
infile datalines dlm = '|';
datalines;
1004|21JUL2022|20|Day 1	 |0|0|0|0|0|0|0|1
1004|03AUG2022|30|Week 2 |0|0|0|0|0|0|0|1
1004|17AUG2022|40|Week 4 |0|0|0|0|0|0|0|1
1004|14SEP2022|50|Week 8 |0|0|0|0|0|0|0|1
;
run;

data want;
   set have2
       have1(where = (AVISIT = 'Unscheduled'));
run;

Now comes the issue of looking up values. Seems to me that the PARAMCD is the only variable you may want to look up? Or do you?

Re: Keeping an unscheduled visit from one dataset in a Hash Object merge

smackerz1988 — Wed, 26 Oct 2022 12:00:44 GMT

well there is additional variables such as aval but I have already set the dataset (have1) to contain just the rows with paramcd ='EASITOTA' from the larger previous dataset

Re: Keeping an unscheduled visit from one dataset in a Hash Object merge

PeterClemmensen — Wed, 26 Oct 2022 12:01:26 GMT

The AVAL variable does not contain any values in the posted sample data so I removed it. If paramcd ='EASITOTA' anyways, there is not need to look it up.

Re: Keeping an unscheduled visit from one dataset in a Hash Object merge

smackerz1988 — Wed, 26 Oct 2022 12:11:55 GMT

I've updated the datasets to try and more accurately reflect what is required

Re: Keeping an unscheduled visit from one dataset in a Hash Object merge

PeterClemmensen — Wed, 26 Oct 2022 12:15:33 GMT

Thank you. Where does the number 18 come from in the want data?

data want;
input SUBJID $ PARAMCD $ ADT :$9. AVISITN :8. AVISIT :$12. AVAL :8. RTT1L28D :8. RTT1G28D :8. SWREL :8.  SWNREL :8. T2CM :8. RTT1L14D :8. RTT1G14D :8. NCUNODEV :8.; 
infile datalines dlm = '|';
datalines;
1004|EASITOTA|21JUL2022|20|Day 1         |16.3|0|0|0|0|0|0|0|1 
1004|EASITOTA|22JUL2022|99|Unscheduled   |18  | | | | | | | |  
1004|EASITOTA|03AUG2022|40|Week 2        |8.6 |0|0|0|0|0|0|0|1 
1004|EASITOTA|17AUG2022|50|Week 4        |8.7 |0|0|0|0|0|0|0|1 
1004|EASITOTA|14SEP2022|60|Week 8        |9.6 |0|0|0|0|0|0|0|1 
1004|EASITOTA|         |70|Week 12       |    |0|0|0|0|0|0|0|1 
1004|EASITOTA|         |80|Week 16       |    |0|0|0|0|0|0|0|1 
;
run;

Re: Keeping an unscheduled visit from one dataset in a Hash Object merge

smackerz1988 — Wed, 26 Oct 2022 12:16:32 GMT

yeah sorry I updated the unscheduled aval in have1 to be 18 also

Re: Keeping an unscheduled visit from one dataset in a Hash Object merge

PeterClemmensen — Wed, 26 Oct 2022 12:22:57 GMT

Better? Now all you have to do is sort the data as you want

data have1;
input  SUBJID $ PARAMCD $ ADT :$9. AVISITN :8. AVISIT :$12. AVAL :8. ;
infile datalines dlm = '|';
datalines;
1004|EASITOTA|21JUL2022|2|Day 1        |16.3 
1004|EASITOTA|22JUL2022|99|Unscheduled |18   
1004|EASITOTA|03AUG2022|4|Week 2       |8.6  
1004|EASITOTA|17AUG2022|5|Week 4       |8.7  
1004|EASITOTA|14SEP2022|6|Week 8       |9.6  
;


data have2;
input  SUBJID $  ADT :$9. AVISITN :8. AVISIT :$12. RTT1L28D :8. RTT1G28D :8. SWREL :8.  SWNREL :8. T2CM :8. RTT1L14D :8. RTT1G14D :8. NCUNODEV :8.; 
infile datalines dlm = '|';
datalines;
1004|21JUL2022|20|Day 1  |0|0|0|0|0|0|0|1 
1004|03AUG2022|40|Week 2 |0|0|0|0|0|0|0|1 
1004|17AUG2022|50|Week 4 |0|0|0|0|0|0|0|1 
1004|14SEP2022|60|Week 8 |0|0|0|0|0|0|0|1 
1004|13OCT2022|70|Week 12|0|0|0|0|0|0|0|1 
1004|10NOV2022|80|Week 16|0|0|0|0|0|0|0|1 
;

data want;

   if _N_ = 1 then do;
      dcl hash h(dataset : 'have1');
      h.definekey('SUBJID','ADT');
      h.definedata('PARAMCD', 'AVAL');
      h.definedone();

   end;

   set have2
       have1(where = (AVISIT = 'Unscheduled'));

   call missing(PARAMCD, AVAL);

   if h.find() then call missing(ADT);

run;

Re: Keeping an unscheduled visit from one dataset in a Hash Object merge

smackerz1988 — Wed, 26 Oct 2022 12:50:53 GMT

Perfect thanks a lot!

Re: Keeping an unscheduled visit from one dataset in a Hash Object merge

PeterClemmensen — Wed, 26 Oct 2022 13:19:16 GMT

Anytime 🙂