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 |
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;
Please provide data in a usable form by following these instructions (not a screen capture, not an attached file)
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 ;
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.
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;
Thank you very much.
I truly appreciate your help @Kurt_Bremser
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: