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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: