Help using Base SAS procedures

copying over from previous data

Reply
Frequent Contributor
Frequent Contributor
Posts: 79

copying over from previous data


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

Respected Advisor
Posts: 3,124

Re: copying over from previous data

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

Frequent Contributor
Frequent Contributor
Posts: 79

Re: copying over from previous data

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!!

Super Contributor
Posts: 307

Re: copying over from previous data

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)?

Ask a Question
Discussion stats
  • 3 replies
  • 211 views
  • 0 likes
  • 3 in conversation