Hi All,
I have a data like Table1,
Table1
pid
date
Amount
1
01/01/2011
0
1
01/01/2011
10
1
01/01/2011
0
1
01/01/2011
0
1
01/01/2011
0
1
01/01/2011
0
2
04/01/2011
0
2
04/01/2011
1000
2
04/01/2011
0
then for each pid I need to extend its record by adding months(0.. max, max=different months between each date and 01jun2011) to create new variable called new date, while amount is equal to its original value, i.e., if original is 0 or missing, then 0 and if original value is 10 ,then 10. The table 2 is what I want.
Table2:
pid
date
Amount
newdate
1
01/01/2011
0
01Jan2011
1
01/01/2011
10
01FEB2011
1
01/01/2011
0
01mar2011
1
01/01/2011
0
01apr2011
1
01/01/2011
0
01May2011
1
01/01/2011
0
01Jun2011
2
04/01/2011
0
01apr2011
2
04/01/2011
1000
01may2011
2
04/01/2011
0
01jun2011
The code which I used to try is as follows, however, its results is not what I want.
proc sort data=t; by pid date; run; data want; set t ; format newdate date9.; by pid date; if first.pid then do; output; do i=0 to intck('month', date, '01Jun2011'd); newdate=intnx('month',date, i); amount = 0; output; end; end; else output; run;
Table3, what I got, but not wanted.
pid
date
Amount
newdate
i
1
01Jan2011
0
1
01Jan2011
0
01Jan2011
0
1
01Jan2011
0
01Feb2011
1
1
01Jan2011
0
01Mar2011
2
1
01Jan2011
0
01Apr2011
3
1
01Jan2011
0
01May2011
4
1
01Jan2011
0
01Jun2011
5
1
01Jan2011
10
1
01Jan2011
0
1
01Jan2011
0
1
01Jan2011
0
1
01Jan2011
0
2
01Apr2011
0
2
01Apr2011
0
01Apr2011
0
2
01Apr2011
0
01May2011
1
2
01Apr2011
0
01Jun2011
2
2
01Apr2011
1000
2
01Apr2011
0
Anyone knows what's wrong with my code and how to correct them to generate the table2?
Thank you so much.
... View more