Using MIN and MAX date to extract the additional row from master table.

[Sathish_jammy]

Hi Community,

Here I have a dataset as a Master table(AAA) where I already fetch some data from Master(AAA) to sub_master table(BBB).

(Table1)

data AAA;
input ID date_A ddmmyy10. grade;
cards;
111111 02/07/2013 1
111111 02/04/2014 1
111111 29/12/2014 .
111111 05/04/2014 .
100000 01/10/2012 .
100000 08/10/2015 2
100000 10/01/2017 5
100000 29/05/2018 2
100000 30/12/2019 .
;
run;

In my BBB table I already fetch some data from AAA table using some condition.   Now the (Table 2) is look like

ID           date_B           GRD

111111   02/07/2013    1
111111    02/04/2014   1

100000  10/01/2017    5
100000  29/05/2018    2

Table 1 = AAA

Table 2 = BBB

Now i have to additionally insert some rows from AAA to BBB.

The condition is

First choose the max(date_B) and find the min(date_A) > max(date_B) and WHERE grade = '.'   

(Which means 111111 29/12/2014 . should affect)

(Which means 100000 30/12/2019 . should affect)

How can I edit my query to get the result? Any help will be much appreciated!

[Haris]

Use nested SQL queries.

select * form A
where date_A GT (select max(date_B) from B)
having date_A=min(date_A) AND grade = '.'

You probably meant to group by ID because the last condition does not make sense other wise.

[VDD · Accepted solution]

First choose the max(date_B) and find the min(date_A) > max(date_B) and WHERE grade = '.'   

(Which means 111111 29/12/2014 . should affect)

(Which means 100000 30/12/2019 . should affect)

are you sure this statement is true for ID 111111 results, would record dated 4-5-2014 be less than 12-29-2014?

I believe this code does what you are asking for correctly.

data Master;
input ID dt : ddmmyy10. grade;
cards;
111111 02/07/2013 1
111111 02/04/2014 1
111111 29/12/2014 .
111111 05/04/2014 .
100000 01/10/2012 .
100000 08/10/2015 .
100000 10/01/2017 5
100000 29/05/2018 1
100000 30/12/2019 .
111122 05/01/1922 .
111122 07/12/1932 .
111122 05/01/1933 .
111122 05/01/1935 .
111122 05/01/2018 1
111122 05/02/2018 .
;
run;
proc sort data=master;
	by id dt;
run;
data sub_master;
input
ID           dt : ddmmyy10.           GRD;
cards;
111111  02/07/2013    1
111111  02/04/2014    1
100000  10/01/2017    5
100000  29/05/2018    2
111122  05/01/2018    1
;
proc sort data=sub_master;
	by id dt;
run;

proc sql;
	create table min_master as
	select distinct a.id, max(a.dt), a.dt, a.grade as grd 
	from master as a,
		 sub_master as b
	where a.grade = . and a.id = b.id and a.dt > b.dt
	group by a.ID, b.id
	having a.dt = min(a.dt)	and b.dt = max(b.dt)
; 
 
quit;

data want;
	set sub_master
		min_master(drop=_TEMG001);
		format dt date10.;
run;

proc sort data=want;
	by id dt;

run;

[Sathish_jammy]

Thanks you so much for your suggestions! @VDD @Haris

[Sathish_jammy]

Hi @VDD 

Your code works well.

But I have a reverse task from the dataset and I tried but it endup up with partial output.

Task 1  (COMPLETED)

First choose the MAX(date_B) and find the MIN(date_A) > MAX(date_B) and WHERE grade = '.' 

TASK 2 (PARTIAL OP)

-----  choose the MIN(date_B) and find the MAX(date_A) < MIN(date_B) and WHERE grade = '.'

The example dataset which I mention in earlier post performs well with TASK2. But not works in my original dataset. 

/*****    TASK 1   **********/ /* You suggested*/ /** COMPLETED**/
Proc Sql;
create table Mater_min as
select distinct a.P_IDA, 
	max(a.Record_Date_A),a.Record_Date_A,a.Grade_A
	from Master as a, SubMaster as b
	where a.Grade_A = '' and a.P_IDA = b.P_IDB and a.Record_Date_A > b.Record_date_B
	group by a.P_IDA,b.P_IDB
	having a.Record_Date_A = min(a.Record_Date_A) and b.Record_Date_B = max(b.Record_date_B);
	quit;

/*******  TASK 2****/  /*What I edited for reverse method*/ /*Results PArtial Output*******/
Proc Sql;
create table Mater_max as
select distinct a.P_IDA, 
	min(a.Record_Date_A),a.Record_Date_A,a.Grade_A
	from Master as a, SubMaster as b
	where a.Grade_A = '' and a.P_IDA = b.P_IDB and a.Record_Date_A < b.Record_Date_B
	group by a.P_IDA,b.P_IDB
	having a.Record_Date_A = max(a.Record_Date_A) and b.Record_Date_B = min(b.Record_date_B);
quit;

HERE I ATTACH the original dataset Please gothrough it and let me know my error.

Thanks in Advance!