How to transfer a field with different values to another table (like a left join with condition)

t34 — Fri, 23 Oct 2020 20:29:42 GMT

I show you what I want to do. I have to tables: have1 and have2 and I want to have the table "want". If date of have2 is between start_date and end_date I want to copy this field

Can you help me with this?

data have1;
informat Start_Date End_Date ddmmyy10.;
format Start_Date End_Date ddmmyy10.;
input id  Start_Date  End_Date;
datalines;
10 08/02/2020 07/03/2020
10 02/10/2020 18/10/2020
;;;;
run;
data have2;
infile datalines delimiter=',';
informat Date ddmmyy10.;
format Date  ddmmyy10.;
input id  Date  ;
datalines;
10,01/01/20
10,15/02/20
10,01/04/20
10,05/05/20
10,05/10/20
;;;;
run;
data want;
infile datalines delimiter=',';
informat Date Start_Date End_Date ddmmyy10.;
format Date Start_Date End_Date ddmmyy10.;
input id  Date  Start_Date  End_Date;
datalines;
10,01/01/20,.,.
10,15/02/20,08/02/2020,07/03/2020
10,01/04/20,.,.
10,05/05/20,.,.
10,05/10/20,02/10/2020,18/10/2020
;;;;
run;

Re: How to transfer a field with different values to another table (like a left join with condition)

jimbarbour — Fri, 23 Oct 2020 22:39:52 GMT

OK, so your results should look like this, yes?

There might be multiple ways to do this, but I would use an array to hold the start and end dates and then index through the array looking for dates that fell between each start and end date range. Sample code is below.

Jim

DATA have1;
	LENGTH	ID	3;
	informat Start_Date End_Date ddmmyy10.;
	format Start_Date End_Date ddmmyy10.;
	input id  Start_Date  End_Date;

	CALL	SYMPUTX	('Nbr_Of_Dates', _N_, 'G');
datalines;
10 08/02/2020 07/03/2020
10 02/10/2020 18/10/2020
;;;;
RUN;

**------------------------------------------------------------------------------**;

DATA have2;
	LENGTH	ID	3;
	infile datalines dsd delimiter=',';
	informat Date ddmmyy10.;
	format Date  ddmmyy10.;
	input id  Date  ;
datalines;
10,01/01/20
10,15/02/20
10,01/04/20
10,05/05/20
10,05/10/20
;;;;
RUN;

**------------------------------------------------------------------------------**;

DATA want;
	DROP	_:;

	FORMAT	ID	Date;

	IF	_N_									=	1		THEN
		DO;
			DO	_i							=	1	TO	&Nbr_Of_Dates;
				SET	Have1	(RENAME=(ID		=	_ID));
				ARRAY	IDs						[&Nbr_Of_Dates]	_TEMPORARY_;
				ARRAY	Starts					[&Nbr_Of_Dates]	_TEMPORARY_;
				ARRAY	Ends					[&Nbr_Of_Dates]	_TEMPORARY_;
				IDs[_i]						=	_ID;
				Starts[_i]					=	Start_Date;
				Ends[_i]					=	End_Date;
*				PUTLOG	"NOTE:  "  _i=  Starts[_i]=  Ends[_i]=;
			END;
		END;

	SET	Have2;

	_Match									=	0;

	DO	_i									=	1	TO	&Nbr_Of_Dates;
		IF	ID								=	IDs[_i]		THEN
			DO;
				IF	Starts[_i]	<	Date	<	Ends[_i]	THEN
					DO;
						_Match				=	1;
						Start_Date			=	Starts[_i];
						End_Date			=	Ends[_i];
						OUTPUT;
					END;
				ELSE
					DO;
					END;
			END;
		ELSE
			DO;
			END;
	END;

	IF	NOT	_Match	THEN
		DO;
			CALL	MISSING(Start_Date, End_Date);
			OUTPUT;
		END;
RUN;

topic How to transfer a field with different values to another table (like a left join with condition) in SAS Programming

How to transfer a field with different values to another table (like a left join with condition)

Re: How to transfer a field with different values to another table (like a left join with condition)