Hi,
I am looking to do the following i have dataset
date myid age
05/31/2000 f10 25
05/31/2000 f20 26
05/31/2001 f10 26
05/31/2001 f20 27
etc
I would like to extend this dataset to include all the months in the middle, keeping the same data intact for the other columns
for example
date myid num
05/31/2000 f10 25
05/31/2000 f20 26
06/30/2000 f10 25
06/30/2000 f20 26
07/30/2000 f10 25
07/30/2000 f20 26
......
05/31/2001 f10 26
05/31/2001 f20 27
06/30/2001 f10 26
06/30/2001 f20 27
etc
Thank you for your help
I suspect that there is more to your request, so not really if I get what you are expecting. An artificial end date is put into a macro variable at the beginning of the code.
%let enddate=31dec2001;
data have;
input date:mmddyy10. myid $ age;
format date: mmddyy10.;
cards;
05/31/2000 f10 25
05/31/2000 f20 26
05/31/2001 f10 26
05/31/2001 f20 27
;
proc sort data=have;
by myid date;
run;
data want;
set have;
by myid;
set have (firstobs=2 keep=date rename=date=_date) have (drop=_all_ obs=1);
if first.myid then do;
do i=0 BY 1 to intck('month',date,_date);
date1=intnx('month',date,i,'end');
output;
end;
end;
if last.myid then do;
do i=1 BY 1 to intck('month',date,"&enddate"d);
date1=intnx('month',date,i,'end');
output;
end;
end;
DROP DATE I _DATE;
RENAME DATE1=DATE;
FORMAT DATE1 MMDDYY10.;
run;
proc sort data=want;
by DATE MYID;
run;
Haikuo
Thank you Haikuo really appreciate it.
yes my question is not very generic , Ill explain my question in more generic terms
If I have a dataset
which has the following columns
dates id number age .... etc
1) I just want to copy over the previous available data for all columns next month if there is no next month date so say my first date is 31may2000, i want to copy over all existing columns to 30jun2000
if there is a 30 jun 2000 then keep that data otherwise copy over the previous month's data
The date specified as you have should be the end date to copy over the data
Thank you so much!!
So the input data may have multiple months for a given ID, but not all months may be present (up to including the "last" desired month)?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.