topic Re: how to extend account sequentially while other variables remain their original values in SAS Programming

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

lucky66 — Thu, 30 Nov 2017 00:35:48 GMT

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.

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

ChrisNZ — Thu, 30 Nov 2017 02:24:22 GMT

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.

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

lucky66 — Thu, 30 Nov 2017 03:12:51 GMT

@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.

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

PGStats — Thu, 30 Nov 2017 03:45:30 GMT

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;

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

ShiroAmada — Thu, 30 Nov 2017 06:10:21 GMT

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;