topic Re: HASH object merge in SAS Programming

HASH object merge

smackerz1988 — Fri, 21 Oct 2022 13:09:48 GMT

Hello I basically want to keep the visit/visitnum from have2 where ANL01FL='Y' and match with visit/visitnum from have1 but also retain the week 12/16 records where aval is missing. Any help with this matter would be greatly appreciated.

data have1;
input  SUBJID $ PARAMCD $ ADT :$9. VISITNUM :8. VISIT :$12. AVAL :8. ;
infile datalines dlm = '|';
datalines;
1004|EASITOTA|27JUN2022|1|Screening|9.6
1004|EASITOTA|21JUL2022|2|Day 1    |16.3
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. VISITNUM :8. VISIT :$12. ANL01FL :$1. RTT1L28D :8. RTT1G28D :8. SWREL :8.  SWNREL :8. T2CM :8. RTT1L14D :8. RTT1G14D :8. NCUNODEV :8.; 
infile datalines dlm = '|';
datalines;
1004|21JUL2022|2|Day 1	|Y|0|0|0|0|0|0|0|1
1004|03AUG2022|4|Week 2	|Y|0|0|0|0|0|0|0|1
1004|17AUG2022|5|Week 4	|Y|0|0|0|0|0|0|0|1
1004|14SEP2022|6|Week 8	|Y|0|0|0|0|0|0|0|1
1004|13OCT2022|7|Week 12|Y|0|0|0|0|0|0|0|1
1004|10NOV2022|8|Week 16|Y|0|0|0|0|0|0|0|1
;
run;


data want;
input SUBJID $ PARAMCD $ ADT :$9. VISITNUM :8. VISIT :$12. RTT1L28D :8. RTT1G28D :8. SWREL :8.  SWNREL :8. T2CM :8. RTT1L14D :8. RTT1G14D :8. NCUNODEV :8.; 
infile datalines dlm = '|';
datalines;
1004|EASITOTA|21JUL2022|2|Day 1	    |16.3|0|0|0|0|0|0|0|1
1004|EASITOTA|03AUG2022|4|Week 2	|8.6 |0|0|0|0|0|0|0|1
1004|EASITOTA|17AUG2022|5|Week 4	|8.7 |0|0|0|0|0|0|0|1
1004|EASITOTA|14SEP2022|6|Week 8	|9.6 |0|0|0|0|0|0|0|1
1004|EASITOTA|         |7|Week 12   |    |0|0|0|0|0|0|0|1
1004|EASITOTA|         |8|Week 16   |    |0|0|0|0|0|0|0|1
;
run;

Re: HASH object merge

PeterClemmensen — Fri, 21 Oct 2022 13:14:05 GMT

None of the records have ANL01FL='Y'?

Re: HASH object merge

smackerz1988 — Fri, 21 Oct 2022 13:15:25 GMT

There is in the have2 dataset but not have1

Re: HASH object merge

PeterClemmensen — Fri, 21 Oct 2022 13:18:44 GMT

No.

Seems to me you switched VISIT and ANL01FL in the have2 data, am I right?

So the have2 data should be

data have2;
input  SUBJID $  ADT :$9. VISITNUM :8. VISIT :$12. ANL01FL :$1. RTT1L28D :8. RTT1G28D :8. SWREL :8.  SWNREL :8. T2CM :8. RTT1L14D :8. RTT1G14D :8. NCUNODEV :8.; 
infile datalines dlm = '|';
datalines;
1004|21JUL2022|2|Day 1	|Y|0|0|0|0|0|0|0|1
1004|03AUG2022|4|Week 2	|Y|0|0|0|0|0|0|0|1
1004|17AUG2022|5|Week 4	|Y|0|0|0|0|0|0|0|1
1004|14SEP2022|6|Week 8	|Y|0|0|0|0|0|0|0|1
1004|13OCT2022|7|Week 12|Y|0|0|0|0|0|0|0|1
1004|10NOV2022|8|Week 16|Y|0|0|0|0|0|0|0|1
;
run;

Re: HASH object merge

smackerz1988 — Fri, 21 Oct 2022 13:20:17 GMT

Sorry yes I did make that update shortly after I posted because I thought it would be easier

Re: HASH object merge

PeterClemmensen — Fri, 21 Oct 2022 13:24:49 GMT

Ok.

I think this gives you what you want

data have1;
input  SUBJID $ PARAMCD $ ADT :$9. VISITNUM :8. VISIT :$12. AVAL :8. ;
infile datalines dlm = '|';
datalines;
1004|EASITOTA|21JUL2022|2|Day 1    |16.3
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. VISITNUM :8. VISIT :$12. ANL01FL :$1. RTT1L28D :8. RTT1G28D :8. SWREL :8.  SWNREL :8. T2CM :8. RTT1L14D :8. RTT1G14D :8. NCUNODEV :8.; 
infile datalines dlm = '|';
datalines;
1004|21JUL2022|2|Day 1	|Y|0|0|0|0|0|0|0|1
1004|03AUG2022|4|Week 2	|Y|0|0|0|0|0|0|0|1
1004|17AUG2022|5|Week 4	|Y|0|0|0|0|0|0|0|1
1004|14SEP2022|6|Week 8	|Y|0|0|0|0|0|0|0|1
1004|13OCT2022|7|Week 12|Y|0|0|0|0|0|0|0|1
1004|10NOV2022|8|Week 16|Y|0|0|0|0|0|0|0|1
;
run;

data want(drop = rc ANL01FL);

   format SUBJID PARAMCD ADT VISITNUM VISIT AVAL RTT1L28D RTT1G28D SWREL SWNREL T2CM RTT1L14D RTT1G14D NCUNODEV;

   if _N_ = 1 then do;
      dcl hash h(dataset : 'have1');
      h.definekey('SUBJID', 'VISITNUM');
      h.definedata(all : 'Y');
      h.definedone();
   end;

   set have2;
   where ANL01FL='Y';

   if 0 then set have1;

   rc = h.find();

   if rc then call missing(ADT, AVAL);

run;

Re: HASH object merge

smackerz1988 — Fri, 21 Oct 2022 13:40:46 GMT

This works great thanks. My only issue is when I apply it to the actual datasets it only populates for the formatted columns but what do you do if there are multiple other column variables in both datasets?. is it the if 0= set statement at the start instead of formats?. Does that make sense?

Re: HASH object merge

PeterClemmensen — Fri, 21 Oct 2022 13:43:19 GMT

What do you mean by it only populates for the formatted columns?
The if 0 then set statement makes the variables from have1 available in the PDV and ready for hash lookup.
The Format Statement at the beginning simply controls the order of variables.

More variables in the data should not be a problem here.

Re: HASH object merge

smackerz1988 — Fri, 21 Oct 2022 13:49:19 GMT

you're 100% correct I know what the issue was now. I think I need to remove the ANL01FL actually from the equation as it's causing unexpected events but if I just remove the where clause referencing ANL01FL it doesn't seem to work. How would you do it if ANL01FL didn't exist or is it the exact same approach?

Re: HASH object merge

PeterClemmensen — Fri, 21 Oct 2022 13:51:40 GMT

Same approach, simply remove it from the Where and Drop statements like this

data have1;
input  SUBJID $ PARAMCD $ ADT :$9. VISITNUM :8. VISIT :$12. AVAL :8. ;
infile datalines dlm = '|';
datalines;
1004|EASITOTA|21JUL2022|2|Day 1    |16.3
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. VISITNUM :8. VISIT :$12. RTT1L28D :8. RTT1G28D :8. SWREL :8.  SWNREL :8. T2CM :8. RTT1L14D :8. RTT1G14D :8. NCUNODEV :8.; 
infile datalines dlm = '|';
datalines;
1004|21JUL2022|2|Day 1	|0|0|0|0|0|0|0|1
1004|03AUG2022|4|Week 2	|0|0|0|0|0|0|0|1
1004|17AUG2022|5|Week 4	|0|0|0|0|0|0|0|1
1004|14SEP2022|6|Week 8	|0|0|0|0|0|0|0|1
1004|13OCT2022|7|Week 12|0|0|0|0|0|0|0|1
1004|10NOV2022|8|Week 16|0|0|0|0|0|0|0|1
;
run;

data want(drop = rc);

   format SUBJID PARAMCD ADT VISITNUM VISIT AVAL RTT1L28D RTT1G28D SWREL SWNREL T2CM RTT1L14D RTT1G14D NCUNODEV;

   if _N_ = 1 then do;
      dcl hash h(dataset : 'have1');
      h.definekey('SUBJID', 'VISITNUM');
      h.definedata(all : 'Y');
      h.definedone();
   end;

   set have2;

   if 0 then set have1;

   rc = h.find();

   if rc then call missing(ADT, AVAL);

run;