topic Re: Expanding dataset from latest date to end date in SAS Programming

Expanding dataset from latest date to end date

Giraffe123 — Thu, 04 Mar 2021 13:53:29 GMT

Hi everyone,

I really need your help on the following, it's breaking my brain:

I have a dataset looking like this

ID	Rating	Date	Enddate
name1	20	201810	202101
name1	30	201811	202101
name1	50	201812	202101
name1	50	201901	202101
name2	40	201901	202101
name2	10	201902	202101
name2	20	201903	202101

Both the date and enddate variables are in sas date format yymmn.

I would like to populate the table for each month using the latest rating up to the enddate (per ID):

ID	Rating	Date	Enddate
name1	20	201810	202101
name1	30	201811	202101
name1	50	201812	202101
name1	50	201901	202101
name1	50	201902	202101
name1	50	201903	202101
name1	50	...	202101
name1	50	202101	202101
name2	40	201901	202101
name2	10	201902	202101
name2	20	201903	202101
name2	20	201904	202101
name2	20	201905	202101
name2	20	...	202101
name2	20	202101	202101

Re: Expanding dataset from latest date to end date

PaigeMiller — Thu, 04 Mar 2021 14:00:15 GMT

Please provide data in a usable form by following these instructions (not a screen capture, not an attached file)

Re: Expanding dataset from latest date to end date

Giraffe123 — Thu, 04 Mar 2021 14:07:07 GMT

data HAVE;
   input ID $ Rating $ Date $ Enddate;
format Date yymmn. Enddate yymmn.;
   datalines;
name1 20 201810 202101
name1 30 201811 202101
name1 50 201812 202101
name1 50 201901 202101
name2 40 201901 202101
name2 10 201902 202101
name2 20 201903 202101
;

Re: Expanding dataset from latest date to end date

Kurt_Bremser — Thu, 04 Mar 2021 14:14:25 GMT

 73         data HAVE;
 74            input ID $ Rating $ Date $ Enddate;
 75         format Date yymmn. Enddate yymmn.;
                        ______
                        484
 NOTE 484-185: Format $YYMMN was not found or could not be loaded.

You need to do something about that.

Read date and enddate as a SAS date with the yymmn6. informat

Re: Expanding dataset from latest date to end date

Kurt_Bremser — Thu, 04 Mar 2021 14:32:04 GMT

If you also need to fill gaps, you need a "look-ahead":

data have;
input ID $ Rating $ (Date Enddate) (:yymmn6.);
format Date Enddate yymmn6.;
datalines;
name1 20 201810 202101
name1 30 201811 202101
name1 50 201812 202101
name1 50 201901 202101
name2 40 201901 202101
name2 10 201902 202101
name2 20 201903 202101
name2 30 201905 202101
;

data want;
merge
  have
  have (
    firstobs=2
    keep=id date
    rename=(id=_id date=_date)
  )
;
output;
if id = _id
then do while (date lt intnx('month',_date,-1,'b'));
  date = intnx('month',date,1,'b');
  output;
end;
else do while (date lt enddate);
  date = intnx('month',date,1,'b');
  output;
end;
drop _id _date;
run;

but if you only need to fill at the end:

data want;
set have;
by id;
output;
if last.id
then do while (date lt enddate);
  date = intnx('month',date,1,'b');
  output;
end;
run;

Re: Expanding dataset from latest date to end date

maguiremq — Thu, 04 Mar 2021 14:38:55 GMT

I don't often use DO loops, so I may be violating some principles. Results weren't as linear as I prefer, but it seems to match what you want. Earlier results may be more comprehensive or streamlined.

data have2;
	set have;
	by id;
	if last.id then do;
		do until (date = enddate);
			date = intnx("month", lag(date), 1);
			output;
		end;
	end;
run;

data want;
	set have
		have2 (where = (date ^= .));
proc sort;
by id;
run;

Re: Expanding dataset from latest date to end date

Giraffe123 — Thu, 04 Mar 2021 14:50:55 GMT

Thank you very much.

I truly appreciate your help @Kurt_Bremser