Assign a variable from value of another variable from another dataset

[Moksha]

Hi All,

       I have two datasets have1 and have2 as below:

data have1;
input studyid$ 1-4 usubjid$ 6-9 visitnum 11-13 visit $13.;
cards;
S001 1001 0   SCREENING
S001 1001 1   Day-1
S001 1001 2   Day1
S001 1001 3   Day2
S001 1001 4   Day3
S001 1001 5   Day4
S001 1001 6   Day5
S001 1001 7   Day6
S001 1001 8   Day7
S001 1001 9   Day8
S001 1001 10  Day9
S001 1001 11  Day10
S001 1001 12  Followup1
S001 1001 99  unscheduled1
S001 1002 0   SCREENING
S001 1002 1   Day-1
S001 1002 2   Day1
S001 1002 3   Day2
S001 1002 4   Day3
S001 1002 5   Day4
S001 1002 6   Day5
S001 1002 7   Day6
S001 1002 8   Day7
S001 1002 9   Day8
S001 1002 10  Day9
S001 1002 11  Day10
S001 1002 12  Followup1
S001 1003 0   SCREENING
S001 1003 1   Day-1
S001 1003 2   Day1
S001 1003 3   Day2
S001 1003 4   Day3
S001 1003 5   Day4
S001 1003 6   Day5
S001 1003 7   Day6
S001 1003 8   Day7
S001 1003 9   Day8
S001 1003 10  Day9
S001 1003 11  Day10
S001 1003 12  Followup1
S001 1003 99  unscheduled1
S001 1004 0   SCREENING
S001 1004 1   Day-1
S001 2001 0   SCREENING
S001 2001 1   Day-1
S001 2001 2   Day1
S001 2001 3   Day2
S001 2001 4   Day3
S001 2001 5   Day4
S001 2001 6   Day5
S001 2001 7   Day6
S001 2001 8   Day7
S001 2001 9   Day8
S001 2001 10  Day9
S001 2001 11  Day10
S001 2001 12  Followup1
S001 2002 0   SCREENING
S001 2002 1   Day-1
S001 2002 2   Day1
S001 2002 3   Day2
S001 2002 4   Day3
S001 2002 5   Day4
S001 2002 6   Day5
S001 2002 7   Day6
S001 2002 8   Day7
S001 2002 9   Day8
S001 2002 10  Day9
S001 2003 0   SCREENING
S001 2003 1   Day-1
S001 2003 2   Day1
S001 2003 3   Day2
S001 2003 4   Day3
S001 2003 5   Day4
S001 2003 6   Day5
S001 2003 7   Day6
S001 2003 8   Day7
S001 2003 9   Day8
S001 2003 10  Day9
S001 2003 11  Day10
S001 2003 12  Followup1
S001 2004 0   SCREENING
S001 3001 0   SCREENING
S001 3002 0   SCREENING
;
run;

proc print data=have1;run;

data have2;
input studyid$ 1-4 visitnum 6-8 visit$ 10-22 visitdy 5. ;
datalines;
S001 0   SCREENING    -28
S001 1   Day-1        -1
S001 2   Day1         1
S001 3   Day2         2
S001 4   Day3         3
S001 5   Day4         4
S001 6   Day5         5
S001 7   Day6         6
S001 8   Day7         7
S001 9   Day8         8
S001 10  Day9         9
S001 11  Day10        10
S001 12  Followup1    14
S001 13  Followup2    18
S001 101 Unscheduled1 
run;

proc print data=have2;run; 

                   I want the output dataset as shown in the attached want,xlsx file.

[PaigeMiller]

Many of us (including me) will not (or can not) download and open Microsoft Office files as they are a security threat. So I cannot see your desired output. Please include the desired output as you have provided the other data, or via screen capture using the "Insert Photos" icon (and not via file attachment).

It would also help if you explained, in words, what the logic is that produces the desired output data set, rather than making us guess and possibly guessing wrong.

[Moksha]

Expected output: Need to create a new dataset by using have1 and have2 as below: 

studyid	usubjid	visinum	visit	visitdy
S001	1001	0	SCREENING	-28
S001	1001	1	Day-1	-1
S001	1001	2	Day1	1
S001	1001	3	Day2	2
S001	1001	4	Day3	3
S001	1001	5	Day4	4
S001	1001	6	Day5	5
S001	1001	7	Day6	6
S001	1001	8	Day7	7
S001	1001	9	Day8	8
S001	1001	10	Day9	9
S001	1001	11	Day10	10
S001	1001	12	Followup1	14
S001	1001	99	unscheduled1
S001	1002	0	SCREENING	-28
S001	1002	1	Day-1	-1
S001	1002	2	Day1	1
S001	1002	3	Day2	2
S001	1002	4	Day3	3
S001	1002	5	Day4	4
S001	1002	6	Day5	5
S001	1002	7	Day6	6
S001	1002	8	Day7	7
S001	1002	9	Day8	8
S001	1002	10	Day9	9
S001	1002	11	Day10	10
S001	1002	12	Followup1	14
S001	1003	0	SCREENING	-28
S001	1003	1	Day-1	-1
S001	1003	2	Day1	1
S001	1003	3	Day2	2
S001	1003	4	Day3	3
S001	1003	5	Day4	4
S001	1003	6	Day5	5
S001	1003	7	Day6	6
S001	1003	8	Day7	7
S001	1003	9	Day8	8
S001	1003	10	Day9	9
S001	1003	11	Day10	10
S001	1003	12	Followup1	14
S001	1003	99	unscheduled1
S001	1004	0	SCREENING	-28
S001	1004	1	Day-1	-1
S001	2001	0	SCREENING	-28
S001	2001	1	Day-1	-1
S001	2001	2	Day1	1
S001	2001	3	Day2	2
S001	2001	4	Day3	3
S001	2001	5	Day4	4
S001	2001	6	Day5	5
S001	2001	7	Day6	6
S001	2001	8	Day7	7
S001	2001	9	Day8	8
S001	2001	10	Day9	9
S001	2001	11	Day10	10
S001	2001	12	Followup1	14
S001	2002	0	SCREENING	-28
S001	2002	1	Day-1	-1
S001	2002	2	Day1	1
S001	2002	3	Day2	2
S001	2002	4	Day3	3
S001	2002	5	Day4	4
S001	2002	6	Day5	5
S001	2002	7	Day6	6
S001	2002	8	Day7	7
S001	2002	9	Day8	8
S001	2002	10	Day9	9
S001	2003	0	SCREENING	-28
S001	2003	1	Day-1	-1
S001	2003	2	Day1	1
S001	2003	3	Day2	2
S001	2003	4	Day3	3
S001	2003	5	Day4	4
S001	2003	6	Day5	5
S001	2003	7	Day6	6
S001	2003	8	Day7	7
S001	2003	9	Day8	8
S001	2003	10	Day9	9
S001	2003	11	Day10	10
S001	2003	12	Followup1	14
S001	2004	0	SCREENING	-28
S001	3001	0	SCREENING	-28
S001	3002	0	SCREENING	-28

[PaigeMiller]

Repeating: It would also help if you explained, in words, what the logic is that produces the desired output data set, rather than making us guess and possibly guessing wrong.

[Moksha]

Sorry, I know only this requirement that using two datasets have1 and have2, need to produce another table want as shown above and not sure of the logic, but I think, following is the Logic by looking at have1 and have2 and want tables: If have1.studyid=have2.studyid and have1.visitnum=have2.visitnum and have1.visit=have2.visit then want.visitdy = have2.visitdy;

Note: Want table will contain all variables studyid, usubjid, visitnum, visit and visitdy

[PaigeMiller]

I'd prefer you work out the logic so that you are sure and then explain it to me, rather than saying "not sure of the logic, but I think, following is the Logic..." This makes me think I will be spending time trying to figure this out, only to have you determine that logic is something different.

[Moksha]

Yes, the logic is :

If have1.visitnum=have2.visitnum and have1.visit=have2.visit then want.visitdy = have2.visitdy;

Note: Want table will contain all variables studyid, usubjid, visitnum, visit and visitdy

[Moksha]

I have tried the following code, but it's not giving the expected result:

proc sql;
	create table want as
		select a.*,
			case 
				when a.visitnum = b.visitnum and a.visit = b.visit then b.visitdy
			end as visitdy
		from have1 a, have2 b;
quit;

proc print data=want;run;

[Moksha]

I have tried the following code, but it's not giving the expected result:

proc sql;
	create table want as
		select a.*,
			case 
				when a.visitnum = b.visitnum and a.visit = b.visit then b.visitdy
			end as visitdy
		from have1 a, have2 b;
quit;

proc print data=want;run;

 Please, help 

[Moksha · Accepted solution]

I have tried with as below and it's working as expected:

proc sort data=have1 out=have1_sort;
by VISITNUM VISIT;
run;

proc print data=have1_sort;run;

proc sort data=have2 out=have2_sort;
by VISITNUM VISIT;
run;

proc print data=have2_sort;run;

data want1;
	merge have1_sort(in=a) have2_sort(in=b);
	by VISITNUM VISIT;

	if a;
run;

proc print data=want1;run;

proc sort data=want1 out=want;
by STUDYID USUBJID VISITNUM;
run;

proc print data=want;run;