topic Re: Reducing Dataset to minimum records in SAS Programming

Reducing Dataset to minimum records

rahul11124 — Thu, 23 Dec 2021 05:53:52 GMT

I have a dataset i.e. -

Coverage_Start Termination_Date Member_Id
24-Jul-19 1-Jun-21 42968701
24-Jul-19 1-Mar-21 42968701
29-Feb-20 1-Mar-20 42968701
16-Feb-19 1-Mar-19 42968701
1-Mar-17 1-Mar-18 42968701
1-Mar-16 1-Mar-17 42968701
1-Dec-15 31-Dec-16 42968701

I want to reduce this dataset, suppose in last three rows minimum coverage_start- 1-Dec-15 and maximum termination_date- 1-Mar-18, so I want to combine all three bottom rows because it has continuous coverage. As result the bottom three rows will be reduced to "1-Dec-15 1-Mar-18 42968701".

Reduced Dataset should be like -

Coverage_Start Termination_Date Member_Id
24-Jun-19 1-Jun-21 42968701
16-Feb-19 1-Mar-19 42968701
1-Dec-15 1-Mar-18 42968701c

Can anyone please help me with this? I'm trying this since a very log time but couldn't achieve it.

Reducing database to minimum entries

rahul11124 — Wed, 22 Dec 2021 13:43:48 GMT

I have a dataset i.e. -

I want to reduce this dataset, suppose in last three rows minimum coverage_start- 1-Dec-15 and maximum termination_date- 1-Mar-18, so I want to combine all three bottom rows because it has continuous coverage.
As result the bottom three rows will be reduced to "1-Dec-15 1-Mar-18 42968701".

Reduced Dataset should be like -

Coverage_Start Termination_Date Member_Id
24-Jun-19 1-Jun-21 42968701
16-Feb-19 1-Mar-19 42968701
1-Dec-15 1-Mar-18 42968701

I want to achieve this task using SAS programming.
Can anyone please help me with this? I'm trying this since a very log time but couldn't achieve it.

Reducing database to minimum entries

rahul11124 — Wed, 22 Dec 2021 13:51:25 GMT

I have a dataset i.e. -

Reduced Dataset should be like -

Coverage_Start Termination_Date Member_Id
24-Jun-19 1-Jun-21 42968701
16-Feb-19 1-Mar-19 42968701
1-Dec-15 1-Mar-18 42968701c

Can anyone please help me with this? I'm trying this since a very log time but couldn't achieve it.

Re: Reducing Dataset to minimum records

PeterClemmensen — Thu, 23 Dec 2021 06:43:22 GMT

Three questions:

What is your grouping variable here? Member_Id? If so, how do we determine what obs to group in the data below? It has the same member_id for all obs.
How does Coverage_Start = 24-Jun-19 appear in the first obs of your desired result?
How does Member_ID = 42968701c appear in the last obs of your desired result?

Re: Reducing Dataset to minimum records

rahul11124 — Thu, 23 Dec 2021 08:11:31 GMT

1. Yes, the grouping vairable is member_id.

2. For first two observations, since there is no break between them so took the minimum of coverage_start and maximum of termination_date in the desired output.

3. Member_id in last obs should be 42968701. Sorry for the typo.

Re: Reducing Dataset to minimum records

andreas_lds — Thu, 23 Dec 2021 08:43:47 GMT

Some questions:

Do you have sas-dates or strings? If you have strings the first step is fixing this along with a format that shows all four digits of the year.
Why are you combining the last three rows? How are groups identified?

Re: Reducing Dataset to minimum records

rahul11124 — Thu, 23 Dec 2021 10:00:55 GMT

1. No. Dates are in date9. format.

2. I have a dataset having millions of row I want reduce the number rows against each member_id.

The above obs are sample of rows against a member.

Re: Reducing Dataset to minimum records

Kurt_Bremser — Thu, 23 Dec 2021 10:57:56 GMT

See this:

data have;
input (Coverage_Start Termination_Date) (:date9.) Member_Id $;
format coverage_start termination_date yymmdd10.;
datalines;
24-Jul-19 1-Jun-21 42968701
24-Jul-19 1-Mar-21 42968701
29-Feb-20 1-Mar-20 42968701
16-Feb-19 1-Mar-19 42968701
1-Mar-17 1-Mar-18 42968701
1-Mar-16 1-Mar-17 42968701
1-Dec-15 31-Dec-16 42968701
;

proc sort data=have;
by member_id coverage_start termination_date;
run;

data want;
set have;
by member_id;
set
  have (
    firstobs=2
    keep=member_id coverage_start
    rename=(member_id=_mem coverage_start=_start)
  )
  have (
    obs=1
    keep=member_id coverage_start
    rename=(member_id=_mem coverage_start=_start)
  )
;
retain start;
if first.member_id then start = coverage_start;
if last.member_id or _start gt termination_date
then do;
  coverage_start = start;
  output;
  start = _start;
end;
else put _all_;
format start yymmdd10.;
keep member_id coverage_start termination_date;
run;

The second SET with FIRSTOBS=2 creates a "look-ahead"; reading have again with OBS=1 is necessary to prevent a premature termination of the data step when OBS - 1 observations are read.

Please note how I presented source data in a data step with datalines; do so yourself in the future to prevent all questions about data type, formats, raw content etc.

Re: Reducing database to minimum entries

Ksharp — Thu, 23 Dec 2021 11:57:43 GMT

data have;
input Coverage_Start : date11. Termination_Date : date11. Member_Id;
format Coverage_Start  Termination_Date  date11. ;
cards;
24-Jul-19 1-Jun-21 42968701
24-Jul-19 1-Mar-21 42968701
29-Feb-20 1-Mar-20 42968701
16-Feb-19 1-Mar-19 42968701
1-Mar-17 1-Mar-18 42968701
1-Mar-16 1-Mar-17 42968701
1-Dec-15 31-Dec-16 42968701
;

data temp;
 set have;
 do date=Coverage_Start to Termination_Date ;
  output;
 end;
keep date Member_Id;
run;
proc sort data=temp nodupkey;by Member_Id date;run;
data temp ;
 set temp;
 by Member_Id;
 if first.Member_Id or dif(date) ne 1 then group+1;
run;
proc sql;
create table want as
select group,max(Member_Id) as Member_Id,
 min(date) as Coverage_Start format=date11.,
 max(date) as Termination_Date  format=date11.
 from temp
  group by group;
quit;

Re: Reducing Dataset to minimum records

Ksharp — Thu, 23 Dec 2021 12:23:08 GMT

data have;
input Coverage_Start : date11. Termination_Date : date11. Member_Id;
format Coverage_Start  Termination_Date  date11. ;
cards;
24-Jul-19 1-Jun-21 42968701
24-Jul-19 1-Mar-21 42968701
29-Feb-20 1-Mar-20 42968701
16-Feb-19 1-Mar-19 42968701
1-Mar-17 1-Mar-18 42968701
1-Mar-16 1-Mar-17 42968701
1-Dec-15 31-Dec-16 42968701
;

data temp;
 set have;
 do date=Coverage_Start to Termination_Date ;
  output;
 end;
keep date Member_Id;
run;
proc sort data=temp nodupkey;by Member_Id date;run;
data temp ;
 set temp;
 by Member_Id;
 if first.Member_Id or dif(date) ne 1 then group+1;
run;
proc sql;
create table want as
select group,max(Member_Id) as Member_Id,
 min(date) as Coverage_Start format=date11.,
 max(date) as Termination_Date  format=date11.
 from temp
  group by group;
quit;

Re: Reducing Dataset to minimum records

rahul11124 — Thu, 23 Dec 2021 13:27:57 GMT

This works but not the efficient one if we run this code on millions of row it will generate around 365 date for each row in do loop

Re: Reducing Dataset to minimum records

rahul11124 — Thu, 23 Dec 2021 13:33:15 GMT

Not getting the correct output in the last row.
Getting -
Coverage_Start Termination_Date Member_Id
24JUL2019 01MAR2020 W22442968701

Want -
Coverage_Start Termination_Date Member_Id
24JUL2019 01Jun2021 W22442968701

Re: Reducing Dataset to minimum records

Kurt_Bremser — Thu, 23 Dec 2021 14:08:09 GMT

Tweaked code:

data want;
set have;
by member_id;
set
  have (
    firstobs=2
    keep=member_id coverage_start
    rename=(member_id=_mem coverage_start=_start)
  )
  have (
    obs=1
    keep=member_id coverage_start
    rename=(member_id=_mem coverage_start=_start)
  )
;
retain start term;
if first.member_id
then do;
  start = coverage_start;
  term = termination_date;
end;
term = max(termination_date,term);
put _all_;
if last.member_id or _start gt term
then do;
  coverage_start = start;
  termination_date = term;
  output;
  start = _start;
  term = '01jan1900'd;
end;
format start term yymmdd10.;
keep member_id coverage_start termination_date;
run;

Re: Reducing database to minimum entries

Kurt_Bremser — Thu, 23 Dec 2021 14:16:53 GMT

I merged your questions. Please do not double-post.

Re: Reducing Dataset to minimum records

Ksharp — Fri, 24 Dec 2021 13:04:27 GMT

OK. How big table do you have ?

If you really care about efficiency problem , try this one .

data have;
input Coverage_Start : date11. Termination_Date : date11. Member_Id;
format Coverage_Start  Termination_Date  date11. ;
cards;
24-Jul-19 1-Jun-21 42968701
24-Jul-19 1-Mar-21 42968701
29-Feb-20 1-Mar-20 42968701
16-Feb-19 1-Mar-19 42968701
1-Mar-17 1-Mar-18 42968701
1-Mar-16 1-Mar-17 42968701
1-Dec-15 31-Dec-16 42968701
;

data want;
 array date{99999} _temporary_ ;
do until(last.Member_Id);
 set have;
 by Member_Id;
 do i=Coverage_Start to Termination_Date;
   date{i}=1;min=min(min,i);max=max(max,i);
 end;
end;
 do i=min to max;
  if missing(date{i-1}) and not missing(date{i}) then start=i;
  if missing(date{i+1}) and not missing(date{i}) then do;end=i;output;end;
 end;
call missing(of date{*});
 format start end date11.;
 keep Member_Id start end;
run;

Re: Reducing database to minimum entries

sbxkoenk — Fri, 24 Dec 2021 15:42:03 GMT

Hello,

> I want to achieve this task using SAS programming.

I have moved your question to the "SAS programming" board.

A question like this is answered in the programming board in an instant.
I'll have a look at it on Monday (but you'll most probably have an answer by then).

Also (this is a suggestion for your next post)
, always provide us with your example data by means of a data step with datalines, like here :

data work.have;
informat Coverage_Start Termination_Date date9.;
format   Coverage_Start Termination_Date date11.;
input Coverage_Start Termination_Date Member_Id $;
datalines;
24-Jul-19 1-Jun-21 42968701
24-Jul-19 1-Mar-21 42968701
29-Feb-20 1-Mar-20 42968701
16-Feb-19 1-Mar-19 42968701
1-Mar-17  1-Mar-18 42968701
1-Mar-16  1-Mar-17 42968701
1-Dec-15 31-Dec-16 42968701
;
run;

Koen

Re: Reducing database to minimum entries

andreas_lds — Mon, 27 Dec 2021 08:22:00 GMT

I have merged your post, please don't post the same issue multiple times, this will only cause confusion and you won't get help any faster.