I have two datasets. One is the SV dataset which contains study visit information (VISIT,VISITNUM,VISITDY) for all subjects in a study. The second dataset is a questionnaire study that has all these visit information columns blank. I'm trying to use hash object to populate these columns by subject based on the analysis date (ADT) and study visit date (SVDT) where applicable. Here is an example of a subjects information
SV
USUBJID | VISITNUM | VISIT | VISITDY | ADT |
1001 | 1 | Screening | -28 | 29Mar2022 |
1001 | 2 | Day 1 | 1 | 27Apr2022 |
1001 | 4 | Week 2 | 15 | 11May2022 |
1001 | 5 | Week 4 | 29 | 26May2022 |
1001 | 6 | Week 8 | 57 | 22Jun2022 |
1001 | 7 | Week 12 | 85 | 20Jul2022 |
QS
USUBJID | VISITNUM | VISIT | VISITDY | SVDT | QSDY |
1001 | 27Apr2022 | 1 | |||
1001 | 28Apr2022 | 2 | |||
1001 | 29Apr2022 | 3 | |||
1001 | 30Apr2022 | 4 | |||
1001 | 01May2022 | 5 | |||
1001 | 02May2022 | 6 | |||
1001 | 03May2022 | 7 | |||
1001 | 04May2022 | 8 | |||
1001 | 05May2022 | 9 | |||
1001 | 06May2022 | 10 | |||
1001 | 07May2022 | 11 | |||
1001 | 08May2022 | 12 | |||
1001 | 09May2022 | 13 | |||
1001 | 10May2022 | 14 | |||
1001 | 11May2022 | 15 | |||
1001 | 12May2022 | 16 | |||
1001 | 13May2022 | 17 | |||
1001 | 14May2022 | 18 | |||
1001 | 15May2022 | 19 | |||
1001 | 16May2022 | 20 | |||
1001 | 17May2022 | 21 | |||
1001 | 18May2022 | 22 | |||
1001 | 19May2022 | 23 | |||
1001 | 20May2022 | 24 | |||
1001 | 21May2022 | 25 | |||
1001 | 22May2022 | 26 | |||
1001 | 23May2022 | 27 | |||
1001 | 24May2022 | 28 | |||
1001 | 25May2022 | 29 | |||
1001 | 26May2022 | 30 | |||
1001 | 27May2022 | 31 | |||
1001 | 28May2022 | 32 | |||
1001 | 29May2022 | 33 | |||
1001 | 30May2022 | 34 | |||
1001 | 01Jun2022 | 36 | |||
1001 | 02Jun2022 | 37 | |||
1001 | 03Jun2022 | 38 | |||
1001 | 04Jun2022 | 39 | |||
1001 | 05Jun2022 | 40 | |||
1001 | 06Jun2022 | 41 | |||
1001 | 07Jun2022 | 42 | |||
1001 | 08Jun2022 | 43 | |||
1001 | 09Jun2022 | 44 | |||
1001 | 10Jun2022 | 45 | |||
1001 | 11Jun2022 | 46 | |||
1001 | 12Jun2022 | 47 | |||
1001 | 13Jun2022 | 48 | |||
1001 | 14Jun2022 | 49 | |||
1001 | 15Jun2022 | 50 | |||
1001 | 16Jun2022 | 51 | |||
1001 | 17Jun2022 | 52 | |||
1001 | 18Jun2022 | 53 | |||
1001 | 19Jun2022 | 54 | |||
1001 | 20Jun2022 | 55 | |||
1001 | 21Jun2022 | 56 | |||
1001 | 22Jun2022 | 57 | |||
1001 | 23Jun2022 | 58 | |||
1001 | 24Jun2022 | 59 | |||
1001 | 25Jun2022 | 60 | |||
1001 | 26Jun2022 | 61 | |||
1001 | 27Jun2022 | 62 | |||
1001 | 28Jun2022 | 63 | |||
1001 | 29Jun2022 | 64 | |||
1001 | 30Jun2022 | 65 | |||
1001 | 01Jul2022 | 66 | |||
1001 | 02Jul2022 | 67 | |||
1001 | 03Jul2022 | 68 | |||
1001 | 04Jul2022 | 69 | |||
1001 | 05Jul2022 | 70 | |||
1001 | 06Jul2022 | 71 | |||
1001 | 07Jul2022 | 72 | |||
1001 | 08Jul2022 | 73 | |||
1001 | 09Jul2022 | 74 | |||
1001 | 10Jul2022 | 75 | |||
1001 | 11Jul2022 | 76 | |||
1001 | 12Jul2022 | 77 | |||
1001 | 13Jul2022 | 78 | |||
1001 | 14Jul2022 | 79 | |||
1001 | 15Jul2022 | 80 | |||
1001 | 16Jul2022 | 81 | |||
1001 | 17Jul2022 | 82 | |||
1001 | 18Jul2022 | 83 | |||
1001 | 19Jul2022 | 84 | |||
1001 | 20Jul2022 | 85 |
Desired output
USUBJID | VISITNUM | VISIT | VISITDY | SVDT | QSDY |
1001 | 2 | Day 1 | 1 | 27Apr2022 | 1 |
1001 | 28Apr2022 | 2 | |||
1001 | 29Apr2022 | 3 | |||
1001 | 30Apr2022 | 4 | |||
1001 | 01May2022 | 5 | |||
1001 | 02May2022 | 6 | |||
1001 | 03May2022 | 7 | |||
1001 | 04May2022 | 8 | |||
1001 | 05May2022 | 9 | |||
1001 | 06May2022 | 10 | |||
1001 | 07May2022 | 11 | |||
1001 | 08May2022 | 12 | |||
1001 | 09May2022 | 13 | |||
1001 | 10May2022 | 14 | |||
1001 | 4 | Week 2 | 15 | 11May2022 | 15 |
1001 | 12May2022 | 16 | |||
1001 | 13May2022 | 17 | |||
1001 | 14May2022 | 18 | |||
1001 | 15May2022 | 19 | |||
1001 | 16May2022 | 20 | |||
1001 | 17May2022 | 21 | |||
1001 | 18May2022 | 22 | |||
1001 | 19May2022 | 23 | |||
1001 | 20May2022 | 24 | |||
1001 | 21May2022 | 25 | |||
1001 | 22May2022 | 26 | |||
1001 | 23May2022 | 27 | |||
1001 | 24May2022 | 28 | |||
1001 | 25May2022 | 29 | |||
1001 | 5 | Week 4 | 29 | 26May2022 | 30 |
1001 | 27May2022 | 31 | |||
1001 | 28May2022 | 32 | |||
1001 | 29May2022 | 33 | |||
1001 | 30May2022 | 34 | |||
1001 | 01Jun2022 | 36 | |||
1001 | 02Jun2022 | 37 | |||
1001 | 03Jun2022 | 38 | |||
1001 | 04Jun2022 | 39 | |||
1001 | 05Jun2022 | 40 | |||
1001 | 06Jun2022 | 41 | |||
1001 | 07Jun2022 | 42 | |||
1001 | 08Jun2022 | 43 | |||
1001 | 09Jun2022 | 44 | |||
1001 | 10Jun2022 | 45 | |||
1001 | 11Jun2022 | 46 | |||
1001 | 12Jun2022 | 47 | |||
1001 | 13Jun2022 | 48 | |||
1001 | 14Jun2022 | 49 | |||
1001 | 15Jun2022 | 50 | |||
1001 | 16Jun2022 | 51 | |||
1001 | 17Jun2022 | 52 | |||
1001 | 18Jun2022 | 53 | |||
1001 | 19Jun2022 | 54 | |||
1001 | 20Jun2022 | 55 | |||
1001 | 21Jun2022 | 56 | |||
1001 | 6 | Week 8 | 57 | 22Jun2022 | 57 |
1001 | 23Jun2022 | 58 | |||
1001 | 24Jun2022 | 59 | |||
1001 | 25Jun2022 | 60 | |||
1001 | 26Jun2022 | 61 | |||
1001 | 27Jun2022 | 62 | |||
1001 | 28Jun2022 | 63 | |||
1001 | 29Jun2022 | 64 | |||
1001 | 30Jun2022 | 65 | |||
1001 | 01Jul2022 | 66 | |||
1001 | 02Jul2022 | 67 | |||
1001 | 03Jul2022 | 68 | |||
1001 | 04Jul2022 | 69 | |||
1001 | 05Jul2022 | 70 | |||
1001 | 06Jul2022 | 71 | |||
1001 | 07Jul2022 | 72 | |||
1001 | 08Jul2022 | 73 | |||
1001 | 09Jul2022 | 74 | |||
1001 | 10Jul2022 | 75 | |||
1001 | 11Jul2022 | 76 | |||
1001 | 12Jul2022 | 77 | |||
1001 | 13Jul2022 | 78 | |||
1001 | 14Jul2022 | 79 | |||
1001 | 15Jul2022 | 80 | |||
1001 | 16Jul2022 | 81 | |||
1001 | 17Jul2022 | 82 | |||
1001 | 18Jul2022 | 83 | |||
1001 | 19Jul2022 | 84 | |||
1001 | 7 | Week 12 | 85 | 20Jul2022 | 85 |
Here is the code I'm currently using but I feel i need to incorporate the QSDY day variable maybe as the results are not correct
data svmerge;
if 0 then
set qs sv;
if _N_ = 1 then
do;
declare hash w(dataset:'sv');
w.defineKey('svdt');
w.defineData('visitnum', 'visit', 'visitdy');
w.defineDone();
end;
set qs_start;
if w.find(key:adt) = 0 then
output;
run;
Try this
data svmerge(drop = rc);
if _N_ = 1 then do;
declare hash w(dataset : 'sv(rename = ADT = svdt');
w.defineKey('USUBJID', 'svdt');
w.defineData('visitnum', 'visit', 'visitdy');
w.defineDone();
end;
set QS;
rc = w.find();
run;
Sample data as data steps:
data SV;
input USUBJID $ VISITNUM VISIT :$10. VISITDY ADT :date9.;
format ADT date9.;
infile datalines dlm = '|';
datalines;
1001|1|Screening|-28|29Mar2022
1001|2|Day 1 |1 |27Apr2022
1001|4|Week 2 |15 |11May2022
1001|5|Week 4 |29 |26May2022
1001|6|Week 8 |57 |22Jun2022
1001|7|Week 12 |85 |20Jul2022
;
data QS;
input USUBJID $ SVDT :date9. QSDY;
VISITNUM = .;
length VISIT $ 50;
VISIT = '';
VISITDY = .;
datalines;
1001 27Apr2022 1
1001 28Apr2022 2
1001 29Apr2022 3
1001 30Apr2022 4
1001 01May2022 5
1001 02May2022 6
1001 03May2022 7
1001 04May2022 8
1001 05May2022 9
1001 06May2022 10
1001 07May2022 11
1001 08May2022 12
1001 09May2022 13
1001 10May2022 14
1001 11May2022 15
1001 12May2022 16
1001 13May2022 17
1001 14May2022 18
1001 15May2022 19
1001 16May2022 20
1001 17May2022 21
1001 18May2022 22
1001 19May2022 23
1001 20May2022 24
1001 21May2022 25
1001 22May2022 26
1001 23May2022 27
1001 24May2022 28
1001 25May2022 29
1001 26May2022 30
1001 27May2022 31
1001 28May2022 32
1001 29May2022 33
1001 30May2022 34
1001 01Jun2022 36
1001 02Jun2022 37
1001 03Jun2022 38
1001 04Jun2022 39
1001 05Jun2022 40
1001 06Jun2022 41
1001 07Jun2022 42
1001 08Jun2022 43
1001 09Jun2022 44
1001 10Jun2022 45
1001 11Jun2022 46
1001 12Jun2022 47
1001 13Jun2022 48
1001 14Jun2022 49
1001 15Jun2022 50
1001 16Jun2022 51
1001 17Jun2022 52
1001 18Jun2022 53
1001 19Jun2022 54
1001 20Jun2022 55
1001 21Jun2022 56
1001 22Jun2022 57
1001 23Jun2022 58
1001 24Jun2022 59
1001 25Jun2022 60
1001 26Jun2022 61
1001 27Jun2022 62
1001 28Jun2022 63
1001 29Jun2022 64
1001 30Jun2022 65
1001 01Jul2022 66
1001 02Jul2022 67
1001 03Jul2022 68
1001 04Jul2022 69
1001 05Jul2022 70
1001 06Jul2022 71
1001 07Jul2022 72
1001 08Jul2022 73
1001 09Jul2022 74
1001 10Jul2022 75
1001 11Jul2022 76
1001 12Jul2022 77
1001 13Jul2022 78
1001 14Jul2022 79
1001 15Jul2022 80
1001 16Jul2022 81
1001 17Jul2022 82
1001 18Jul2022 83
1001 19Jul2022 84
1001 20Jul2022 85
;
A few things:
Anyways, here is how I would do it.
data svmerge;
if _N_ = 1 then do;
declare hash w(dataset : 'sv(rename = ADT = svdt');
w.defineKey('USUBJID', 'svdt');
w.defineData('visitnum', 'visit', 'visitdy');
w.defineDone();
end;
set QS;
if w.find() = 0;
run;
How does my codes result differ from what you want?
I don't understand. If a value does not match in QS, then it is not included in the output data set? Isn't that the whole point?
Otherwise, please show an example of you desired result from the posted sample data.
I've updated the question to include the desired output. Thanks for clarifying. Basically I need records with matching visits to derive a parameter calculation but still need to keep all the records as it contains questionnaire data that happened in between scheduled visits
Try this
data svmerge(drop = rc);
if _N_ = 1 then do;
declare hash w(dataset : 'sv(rename = ADT = svdt');
w.defineKey('USUBJID', 'svdt');
w.defineData('visitnum', 'visit', 'visitdy');
w.defineDone();
end;
set QS;
rc = w.find();
run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.