BookmarkSubscribeRSS Feed
lucky66
Calcite | Level 5

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. 

 

4 REPLIES 4
ChrisNZ
Tourmaline | Level 20

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.

lucky66
Calcite | Level 5

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

PGStats
Opal | Level 21

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
ShiroAmada
Lapis Lazuli | Level 10
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 802 views
  • 0 likes
  • 4 in conversation