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.
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.
@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.
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;
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;
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.