topic Re: Merging by visit with hash object in SAS Programming

Merging by visit with hash object

smackerz1988 — Wed, 24 Aug 2022 10:42:05 GMT

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

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

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;

Re: Merging by visit with hash object

PeterClemmensen — Wed, 24 Aug 2022 09:45:46 GMT

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:

If the variables as already present in the QS data set, there is not need to prepare the PDV with the 'if 0 then set' statement.
You probably want to have USUBJID as a key in the hash object too.
I'm not sure why you want QSDY to be part of the lookup. Is that equivalent to VISITNUM in the SV data?

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;

Re: Merging by visit with hash object

smackerz1988 — Wed, 24 Aug 2022 10:00:22 GMT

I'm not sure why you want QSDY to be part of the lookup. Is that equivalent to VISITNUM in the SV data?
it is equivalent to VISITDY so ideally I just want the visit columns to be populated where the visitdy matches with qsdy only. For example, 27Apr2022 is day 1 for both visitdy and qsdy

Re: Merging by visit with hash object

PeterClemmensen — Wed, 24 Aug 2022 10:04:58 GMT

How does my codes result differ from what you want?

Re: Merging by visit with hash object

smackerz1988 — Wed, 24 Aug 2022 10:09:09 GMT

I think the only thing is that I need to retain the values that don't match in QS. So if there is no visit data available I still need to keep that record. Is it a case of adding a call missing statement?

Re: Merging by visit with hash object

PeterClemmensen — Wed, 24 Aug 2022 10:32:15 GMT

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.

Re: Merging by visit with hash object

smackerz1988 — Wed, 24 Aug 2022 10:45:53 GMT

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

Re: Merging by visit with hash object

PeterClemmensen — Wed, 24 Aug 2022 10:58:43 GMT

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;

Re: Merging by visit with hash object

smackerz1988 — Wed, 24 Aug 2022 11:01:19 GMT

Perfect thanks! Plus your other solution I can apply the logic to other situations so that is also appreciated!