topic Re: SCD type2 in SAS in SAS Programming

SCD type2 in SAS

SASAna — Wed, 30 Jun 2021 00:03:56 GMT

Hi SAS Users,

I am trying to rollup the records by member_id util there is a change in 2 fields. I have given 2 scenario examples in the raw and what i tried in my code to get the expected.

Raw data - ( A change in 2 fields ( PLAN & TYPE) , should generate the new line with its begin and end date)

member_id date plan type

123 01JAN2017 A 1

123 01FEB2017 A 1

123 01MAR2017 A 2

234 01JAN2017 A 1

234 01FEB2017 B 3

234 01DEC2017 B 3

234 01JAN2018 B 2

Expected rollup -

member_id start_date end_date plan type

123 01JAN2017 30FEB2017 A 1

123 01MAR2017 31MAR2017 A 2

234 01JAN2017 31JAN2017 A 1

234 01FEB2017 31DEC2017 B 3

234 01JAN2018 31JAN2018 B 2

proc sort data=test; by member_id date ; run;

data test;
length PLAN_TYPE $10.;
set test;
format start_date end_date date9. ;
plan_type = trim(plan) || trim(type);
by member_id;
retain plan_type plan_type _tmp;
if first.member_idthen
do;
plan_type_tmp= plan_type ;
begin_date = date;
end_date = intnx('month',fisc_dt,1)-1;
end_date1 =end_Date+1;
end;
else if date= end_date1 then
do;
if trim(plan_type_tmp) ne trim(plan_type ) then do; start_date = date;

plan_type_tmp= trim(plan_type );

end;
else
end_date = date;
end;
if last.member_id then output;
run;

Re: SCD type2 in SAS

ChrisNZ — Wed, 30 Jun 2021 01:35:07 GMT

Please:

1. Paste your code using the appropriate icon

2. Provide data as code (data step or sql)

This does what you want and comes after your proc sort:

data WANT;
  set HAVE;
  by ID PLAN TYPE notsorted;
  format START_DATE END_DATE date9.;
  retain _DATE;
  if first.TYPE then _DATE=DATE;
  if last.TYPE then do;
    START_DATE=_DATE;
    END_DATE=intnx('month',DATE,0,'e');
    output; 
  end;
run;

Note that 30FEB2017 does not exist. Do test the data and code you provide.

Re: SCD type2 in SAS

SASAna — Wed, 30 Jun 2021 02:25:51 GMT

Thank you. It worked very well.

Re: SCD type2 in SAS

ChrisNZ — Wed, 30 Jun 2021 02:59:45 GMT

Glad it did. Please heed the points raised next time you post.