BookmarkSubscribeRSS Feed
newsas007
Quartz | Level 8

Hi,

Below is my SAS code and the final 'Want' dataset. 

First rule is straight forward and have the code working for it. For example: ID#1 row 1: Line 2 falls between LOstart and  LOend. i.e. 7/16/2016 falls between 4/5/2016 and 8/16/2016

i need help with second rule. If the the ordering date doesn't fall between the specific LOstart and LOend, then search if it is between any 2 consecutive LINE's i.e. if it occurs between LOEnd of a line and beginning of next LOStart. For example ID#5 has a ordering date of 7/31/2016 and it does not fall between and specific date but it does fall between the LoEnd of Line = 2 and LoStart of Line = 3. So therefore it goes in line 2. ID#7 ordering date doesn't fall between LoStart and LoEnd; also doesn't have any consecutive line.

Data Want is below.

 

 

data dat1;
input ID	LINE (LOSTART	LOEND) (:mmddyy10.);
format LINE LOSTART LOEND mmddyy10.;
cards;
1	4	11/27/2017	1/17/2018
1	1	12/1/2015	4/1/2016
1	2	4/5/2016	8/16/2016
1	3	8/23/2016	11/14/2016
2	1	6/4/2016	9/18/2016
2	2	9/19/2016	11/11/2016
3	2	5/6/2016	7/17/2016
3	3	7/18/2016	10/3/2016
4	1	11/2/2015	5/30/2016
4	2	5/31/2016	12/28/2016
4	3	4/8/2018	1/9/2019
5	1	4/3/2015	6/4/2015
5	2	6/5/2015	12/27/2015
5	3	8/5/2016	9/7/2016
6	1	10/12/2015	6/2/2016
6	4	11/7/2016	4/20/2017
6	2	6/3/2016	8/27/2016
7	1	6/5/2016	6/15/2016
;

data dat2;
input ID	Ordering :mmddyy10.;
format ordering mmddyy10.;
cards;
1	7/16/2016
1	10/2/2017
2	7/18/2016
3	7/18/2016
4	7/26/2016
5	7/31/2016
6	7/31/2016
7	5/10/2016
;

proc sql;
create table ab  as
select distinct a.*, b.ordering
from dat1 a left join dat2 b
on a.ID=b.ID and LOSTART<=Ordering<=LOEND
order by a.ID;
quit;

 

Data Want:
ID LINE LOSTART LOEND Ordering 1 First 12/1/2015 4/1/2016 . 1 Fourth 11/27/2017 1/17/2018 . 1 Second 4/5/2016 8/16/2016 7/16/2016 1 Third 8/23/2016 11/14/2016 . 2 First 6/4/2016 9/18/2016 7/18/2016 2 Second 9/19/2016 11/11/2016 . 3 Second 5/6/2016 7/17/2016 . 3 Third 7/18/2016 10/3/2016 7/18/2016 4 First 11/2/2015 5/30/2016 . 4 Second 5/31/2016 12/28/2016 7/26/2016 4 Third 4/8/2018 1/9/2019 . 5 First 4/3/2015 6/4/2015 . 5 Second 6/5/2015 12/27/2015 7/31/2016 5 Third 8/5/2016 9/7/2016 . 6 First 10/12/2015 6/2/2016 . 6 Fourth 11/7/2016 4/20/2017 . 6 Second 6/3/2016 8/27/2016 7/31/2016 7 First 6/5/2016 6/15/2016 .

 

3 REPLIES 3
MayurPhegde
SAS Employee

Check if below code helps: 

 


proc sort data=dat1 out=dat1_1;
by id line;
run;


proc sql;
create table ab as
select distinct a.*, coalesce(b.ordering,d.ordering) fromat=mmddyy10. as ordering
from dat1 a
left join dat2 b
on a.ID=b.ID and LOSTART<=Ordering<=LOEND
left join dat1 c
on a.ID=c.ID and a.line = c.line - 1
left join dat2 d
on c.ID=d.ID and a.LOEND<=d.Ordering<=c.LOSTART
order by a.ID;
quit;

 

Output:

ID LINE LOSTART LOEND ordering
1 1 12/1/2015 4/1/2016 .
1 2 4/5/2016 8/16/2016 7/16/2016
1 3 8/23/2016 11/14/2016 10/2/2017
1 4 11/27/2017 1/17/2018 .
2 1 6/4/2016 9/18/2016 7/18/2016
2 2 9/19/2016 11/11/2016 .
3 2 5/6/2016 7/17/2016 7/18/2016
3 3 7/18/2016 10/3/2016 7/18/2016
4 1 11/2/2015 5/30/2016 .
4 2 5/31/2016 12/28/2016 7/26/2016
4 3 4/8/2018 1/9/2019 .
5 1 4/3/2015 6/4/2015 .
5 2 6/5/2015 12/27/2015 7/31/2016
5 3 8/5/2016 9/7/2016 .
6 1 10/12/2015 6/2/2016 .
6 2 6/3/2016 8/27/2016 7/31/2016
6 4 11/7/2016 4/20/2017 .
7 1 6/5/2016 6/15/2016 .
newsas007
Quartz | Level 8

@MayurPhegde : Thank you for this. There seems to be some issue with this. For example, the code below outputs ordering date (7/18/2016) twice for ID#3.

 

data dat1;
input ID	LINE	(LOSTART	LOEND) (:mmddyy10.);
format 	LOSTART	LOEND mmddyy10.;
cards;
1	4	11/27/2017	1/17/2018
1	1	12/1/2015	4/1/2016
1	2	4/5/2016	8/16/2016
1	3	8/23/2016	11/14/2016
2	1	6/4/2016	9/18/2016
2	2	9/19/2016	11/11/2016
3	2	5/6/2016	7/17/2016
3	3	7/18/2016	10/3/2016
4	1	11/2/2015	5/30/2016
4	2	5/31/2016	12/28/2016
4	3	4/8/2018	1/9/2019
5	1	4/3/2015	6/4/2015
5	2	6/5/2015	12/27/2015
5	3	8/5/2016	9/7/2016
6	1	10/12/2015	6/2/2016
6	4	11/7/2016	4/20/2017
6	2	6/3/2016	8/27/2016
7	1	6/5/2016	6/15/2016
7   2   8/15/2016   9/15/2016
7   3   9/16/2016   10/16/2016 
;

data dat2;
input ID	Ordering :mmddyy10.;
format ordering mmddyy10.;
cards;
1	7/16/2016
1	10/2/2017
2	7/18/2016
3	7/18/2016
4	7/26/2016
5	7/31/2016
6	7/31/2016
7	7/10/2016
;

proc sql;
create table ab as
select distinct a.*, coalesce(b.ordering,d.ordering) format=mmddyy10. as ordering
from dat1 a
left join dat2 b
on a.ID=b.ID and LOSTART<=Ordering<=LOEND
left join dat1 c
on a.ID=c.ID and a.line = c.line - 1
left join dat2 d
on c.ID=d.ID and a.LOEND<=d.Ordering<=c.LOSTART
order by a.ID;
quit;
MayurPhegde
SAS Employee
Its because of below condition
c.ID=d.ID and a.LOEND<=d.Ordering<=c.LOSTART

Replace it with
c.ID=d.ID and a.LOEND<d.Ordering<c.LOSTART


It should work.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 452 views
  • 0 likes
  • 2 in conversation