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

[smackerz1988]

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;

[PeterClemmensen]

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;

[smackerz1988]

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?

[PeterClemmensen]

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

[smackerz1988]

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.

[PeterClemmensen]

Why can't you reverse the lookup logic?

[smackerz1988]

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.

[PeterClemmensen]

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?

[smackerz1988]

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

[PeterClemmensen]

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.

[smackerz1988]

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

[PeterClemmensen]

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;

[smackerz1988]

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

[PeterClemmensen · Accepted solution]

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;

[smackerz1988]

Perfect thanks a lot!