DATA Step, Macro, Functions and more

how to extend account sequentially while other variables remain their original values

Reply
Contributor
Posts: 20

how to extend account sequentially while other variables remain their original values

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. 

 

Super User
Posts: 2,488

Re: how to extend account sequentially while other variables remain their original values

Needs unclear.

You state "then for each pid I need to extend its record"  but you extend the pid=1 dates across pid=2 in your table2 output.

Contributor
Posts: 20

Re: how to extend account sequentially while other variables remain their original values


ChrisNZ wrote:

Needs unclear.

You state "then for each pid I need to extend its record"  but you extend the pid=1 dates across pid=2 in your table2 output.


what I need is shown in Table2:

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

 

1.For each Id's, its newdate starts from its date, then increase one month per row until newdate hit 01Jun2011. 

for instance, pid=1, its date is 01JAN2011, then its first newdate is also 01Jan2011, then increase by one month afterward, until newdate is equal to 01June2011.  For the same logic, pid2, its newdate starts with its date 01apr2011 then extend until 01Jun2011. 

2. At the same time, its amount field value remain unchanged, for instance, for pid1, only its raw second row has value 10, all values in other rows are 0. 

 

Hopefully now it is clear. Thanks. 

Esteemed Advisor
Posts: 5,611

Re: how to extend account sequentially while other variables remain their original values

[ Edited ]

This would do it, I guess

 

 

data t1;
input pid date :mmddyy10. Amount;
format date yymmdd10.;
datalines;
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 
;

data want;
retain newDate;
format newdate yymmdd10.;
set t1; by pid;
if first.pid then newdate = date;
else newdate = intnx("month", newDate, 1);
run;

proc print; run;
PG
Frequent Contributor
Posts: 118

Re: how to extend account sequentially while other variables remain their original values

data HAVE;
  infile datalines dlm="|";
  input
        pid: 8.
        date: date9.
        amount: 8.;
        format date date9.;
datalines;
1|01Jan2011|0
1|01Jan2011|10
1|01Jan2011|0
1|01Jan2011|0
1|01Jan2011|0
1|01Jan2011|0
2|01Apr2011|0
2|01Apr2011|1000
2|01Apr2011|0
;
run;

proc sort data=HAVE;
  by pid date;
run;

data WANT;
  set HAVE;
  by pid;
  if first.pid then inc=-1;

  inc+1;
  format newdate date9.;
  newdate=intnx('month',date,inc);
run;
Ask a Question
Discussion stats
  • 4 replies
  • 175 views
  • 0 likes
  • 4 in conversation