Hi,
I have got a SAS dataset with accno,opendate,close_date.
I have to create a new variable in this dataset where for an iD the opendate increases by one until the close date
close_date | open_date | id |
07-Feb-13 | 03-Dec-12 | 123567 |
02-Dec-11 | 01-Sep-11 | 123568 |
02-Dec-09 | 26-Oct-09 | 123569 |
I need a result like below
id | open_date | close_date |
123567 | 03-Dec-12 | 03-Jan-13 |
123567 | 04-Dec-12 | 03-Jan-13 |
123567 | 05-Dec-12 | 03-Jan-13 |
123567 | 06-Dec-12 | 03-Jan-13 |
123567 | 07-Dec-12 | 03-Jan-13 |
123567 | 08-Dec-12 | 03-Jan-13 |
123567 | 09-Dec-12 | 03-Jan-13 |
123567 | 10-Dec-12 | 03-Jan-13 |
123567 | 11-Dec-12 | 03-Jan-13 |
123567 | 12-Dec-12 | 03-Jan-13 |
123567 | 13-Dec-12 | 03-Jan-13 |
123567 | 14-Dec-12 | 03-Jan-13 |
123567 | 15-Dec-12 | 03-Jan-13 |
123567 | 16-Dec-12 | 03-Jan-13 |
123567 | 17-Dec-12 | 03-Jan-13 |
123567 | 18-Dec-12 | 03-Jan-13 |
123567 | 19-Dec-12 | 03-Jan-13 |
123567 | 20-Dec-12 | 03-Jan-13 |
123567 | 21-Dec-12 | 03-Jan-13 |
123567 | 22-Dec-12 | 03-Jan-13 |
123567 | 23-Dec-12 | 03-Jan-13 |
123567 | 24-Dec-12 | 03-Jan-13 |
123567 | 25-Dec-12 | 03-Jan-13 |
123567 | 26-Dec-12 | 03-Jan-13 |
123567 | 27-Dec-12 | 03-Jan-13 |
123567 | 28-Dec-12 | 03-Jan-13 |
123567 | 29-Dec-12 | 03-Jan-13 |
123567 | 30-Dec-12 | 03-Jan-13 |
123567 | 31-Dec-12 | 03-Jan-13 |
123567 | 01-Jan-13 | 03-Jan-13 |
123567 | 02-Jan-13 | 03-Jan-13 |
123567 | 03-Jan-13 | 03-Jan-13 |
123568 | 01-Sep-11 | 10-Sep-11 |
123568 | 02-Sep-11 | 10-Sep-11 |
123568 | 03-Sep-11 | 10-Sep-11 |
123568 | 04-Sep-11 | 10-Sep-11 |
123568 | 05-Sep-11 | 10-Sep-11 |
123568 | 06-Sep-11 | 10-Sep-11 |
123568 | 07-Sep-11 | 10-Sep-11 |
123568 | 08-Sep-11 | 10-Sep-11 |
123568 | 09-Sep-11 | 10-Sep-11 |
In general:
data want;
set have;
do date = open_date to close_date;
output;
end;
run;
You may need to add some filters/conditions if you have exceptions or other things you need to consider.
@MV04 wrote:
Hi,
I have got a SAS dataset with accno,opendate,close_date.
I have to create a new variable in this dataset where for an iD the opendate increases by one until the close date
close_date open_date id 07-Feb-13 03-Dec-12 123567 02-Dec-11 01-Sep-11 123568 02-Dec-09 26-Oct-09 123569 I need a result like below
id open_date close_date 123567 03-Dec-12 03-Jan-13 123567 04-Dec-12 03-Jan-13 123567 05-Dec-12 03-Jan-13 123567 06-Dec-12 03-Jan-13 123567 07-Dec-12 03-Jan-13 123567 08-Dec-12 03-Jan-13 123567 09-Dec-12 03-Jan-13 123567 10-Dec-12 03-Jan-13 123567 11-Dec-12 03-Jan-13 123567 12-Dec-12 03-Jan-13 123567 13-Dec-12 03-Jan-13 123567 14-Dec-12 03-Jan-13 123567 15-Dec-12 03-Jan-13 123567 16-Dec-12 03-Jan-13 123567 17-Dec-12 03-Jan-13 123567 18-Dec-12 03-Jan-13 123567 19-Dec-12 03-Jan-13 123567 20-Dec-12 03-Jan-13 123567 21-Dec-12 03-Jan-13 123567 22-Dec-12 03-Jan-13 123567 23-Dec-12 03-Jan-13 123567 24-Dec-12 03-Jan-13 123567 25-Dec-12 03-Jan-13 123567 26-Dec-12 03-Jan-13 123567 27-Dec-12 03-Jan-13 123567 28-Dec-12 03-Jan-13 123567 29-Dec-12 03-Jan-13 123567 30-Dec-12 03-Jan-13 123567 31-Dec-12 03-Jan-13 123567 01-Jan-13 03-Jan-13 123567 02-Jan-13 03-Jan-13 123567 03-Jan-13 03-Jan-13 123568 01-Sep-11 10-Sep-11 123568 02-Sep-11 10-Sep-11 123568 03-Sep-11 10-Sep-11 123568 04-Sep-11 10-Sep-11 123568 05-Sep-11 10-Sep-11 123568 06-Sep-11 10-Sep-11 123568 07-Sep-11 10-Sep-11 123568 08-Sep-11 10-Sep-11 123568 09-Sep-11 10-Sep-11
where is close date value 03-Jan-13 in
close_date | open_date | id |
07-Feb-13 | 03-Dec-12 | 123567 |
02-Dec-11 | 01-Sep-11 | 123568 |
02-Dec-09 | 26-Oct-09 | 123569 |
apologies the sample didnot go until 07feb2013 as this will be very long list but yes the open date has to be incremented until 07feb2013
In general:
data want;
set have;
do date = open_date to close_date;
output;
end;
run;
You may need to add some filters/conditions if you have exceptions or other things you need to consider.
@MV04 wrote:
Hi,
I have got a SAS dataset with accno,opendate,close_date.
I have to create a new variable in this dataset where for an iD the opendate increases by one until the close date
close_date open_date id 07-Feb-13 03-Dec-12 123567 02-Dec-11 01-Sep-11 123568 02-Dec-09 26-Oct-09 123569 I need a result like below
id open_date close_date 123567 03-Dec-12 03-Jan-13 123567 04-Dec-12 03-Jan-13 123567 05-Dec-12 03-Jan-13 123567 06-Dec-12 03-Jan-13 123567 07-Dec-12 03-Jan-13 123567 08-Dec-12 03-Jan-13 123567 09-Dec-12 03-Jan-13 123567 10-Dec-12 03-Jan-13 123567 11-Dec-12 03-Jan-13 123567 12-Dec-12 03-Jan-13 123567 13-Dec-12 03-Jan-13 123567 14-Dec-12 03-Jan-13 123567 15-Dec-12 03-Jan-13 123567 16-Dec-12 03-Jan-13 123567 17-Dec-12 03-Jan-13 123567 18-Dec-12 03-Jan-13 123567 19-Dec-12 03-Jan-13 123567 20-Dec-12 03-Jan-13 123567 21-Dec-12 03-Jan-13 123567 22-Dec-12 03-Jan-13 123567 23-Dec-12 03-Jan-13 123567 24-Dec-12 03-Jan-13 123567 25-Dec-12 03-Jan-13 123567 26-Dec-12 03-Jan-13 123567 27-Dec-12 03-Jan-13 123567 28-Dec-12 03-Jan-13 123567 29-Dec-12 03-Jan-13 123567 30-Dec-12 03-Jan-13 123567 31-Dec-12 03-Jan-13 123567 01-Jan-13 03-Jan-13 123567 02-Jan-13 03-Jan-13 123567 03-Jan-13 03-Jan-13 123568 01-Sep-11 10-Sep-11 123568 02-Sep-11 10-Sep-11 123568 03-Sep-11 10-Sep-11 123568 04-Sep-11 10-Sep-11 123568 05-Sep-11 10-Sep-11 123568 06-Sep-11 10-Sep-11 123568 07-Sep-11 10-Sep-11 123568 08-Sep-11 10-Sep-11 123568 09-Sep-11 10-Sep-11
Thanks so much Reeza
It worked
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.