BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
smackerz1988
Pyrite | Level 9

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;

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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;

View solution in original post

15 REPLIES 15
PeterClemmensen
Tourmaline | Level 20

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
Pyrite | Level 9

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
Tourmaline | Level 20

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

smackerz1988
Pyrite | Level 9

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.

smackerz1988
Pyrite | Level 9

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
Tourmaline | Level 20

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
Pyrite | Level 9

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
Tourmaline | Level 20

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
Pyrite | Level 9

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

PeterClemmensen
Tourmaline | Level 20

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
Pyrite | Level 9

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

PeterClemmensen
Tourmaline | Level 20

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;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 15 replies
  • 1950 views
  • 2 likes
  • 2 in conversation