topic Using MIN and MAX date to extract the additional row from master table. in SAS Programming

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

Sathish_jammy — Sat, 22 Sep 2018 12:21:18 GMT

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!

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

Haris — Sat, 22 Sep 2018 16:14:09 GMT

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.

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

VDD — Sat, 22 Sep 2018 16:16:56 GMT

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;

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

Sathish_jammy — Mon, 24 Sep 2018 05:39:13 GMT

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

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

Sathish_jammy — Mon, 24 Sep 2018 12:26:40 GMT

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!