Adding the number of days to a date

[Sandeep77]

Hi all,

I have a dataset which has dt_delaydays and dt_datdelay columns. dt_delaydays is showing the number of days and dt_datdelay is showing the date. I am trying to add a new column (say new_date) which add the number of days (dt_delaydays) to the date (dt_datdelay) and gives the output in a date format in the new_date column. For e.g., 5 + 12/09/2023 will show 17/09/2023 in the new date column. My code does not show any date in the new_date column.

Here is the sample dataset:

Data delayed_accounts;				
infile cards expandtabs;				
input debt_code	rep_code$	dt_delaydays	dt_datdelay:date9.;
format dt_datdelay date9.;
datalines ;	
106444375	133	16	21MAR2013:00:00:00.000
345347595	168	2	23DEC2019:00:00:00.000
323832873	168C	10	24FEB2020:00:00:00.000
178669982	122	30	06SEP2017:00:00:00.000
;				
run;
My code:
Proc sql;
create table delayed_accounts as 
select *,
intnx('day', dt_datdelay, dt_delaydays) as new_date
from Accounts_in_trace;
quit;
Log:
29         Proc sql;
30         create table delayed_accounts as
31         select *,
32         intnx('day', dt_datdelay, dt_delaydays) as new_date
33         from Accounts_in_trace;
NOTE: Invalid argument to function INTNX. Missing values may be generated.
NOTE: Compressing data set WORK.DELAYED_ACCOUNTS decreased size by 23.29 percent. 
      Compressed is 168 pages; un-compressed would require 219 pages.
NOTE: Table WORK.DELAYED_ACCOUNTS created, with 254537 rows and 5 columns.

34         quit;

[yabwon]

Date in SAS (also the one used in Proc SQL) is a "number of days which remained since 1 January 1960", for example 19-OCT-1993 is 12345. 

So if you want to add for example 5 days to a date in SAS you can just write:

/* in Data Step */

newDatevariable = dateVariable + 5 ;

/* or in SQL */

dateVariable + 5 as newDatevariable 

Bart

[yabwon · Accepted solution]

Your code, modified:

Data delayed_accounts;				
infile cards expandtabs;				
input debt_code	rep_code$	dt_delaydays	dt_datdelay:date9.;
format dt_datdelay date9.;
datalines ;	
106444375	133	16	21MAR2013:00:00:00.000
345347595	168	2	23DEC2019:00:00:00.000
323832873	168C	10	24FEB2020:00:00:00.000
178669982	122	30	06SEP2017:00:00:00.000
;				
run;


Proc sql;
create table delayed_accounts_2 as
select *,
dt_datdelay + dt_delaydays as new_date format yymmdd10.
from delayed_accounts;
quit;

Bart

[Sandeep77]

Thank you @yabwon  but the code that you suggested, does not show any date in the output data. Please see the sample output data.

Data Delayed_accounts_2;				
infile cards expandtabs;				
input debt_code	rep_code$	dt_delaydays	dt_datdelay:date9.	new_date:date9.;
format dt_datdelay date9.;
datalines ;	
106444375	133	16	21MAR2013:00:00:00.000	.
345347595	168	2	23DEC2019:00:00:00.000	.
323832873	168C	10	24FEB2020:00:00:00.000	.
178669982	122	30	06SEP2017:00:00:00.000	.
;							
run;

[yabwon]

I dare to disagree (see screen shot below). 

Did you go with Maxim2 and Maxim3 ?

Bart

[Kurt_Bremser]

---

@Sandeep77 wrote:

Thank you @yabwon  but the code that you suggested, does not show any date in the output data. Please see the sample output data.

Data Delayed_accounts_2;				
infile cards expandtabs;				
input debt_code	rep_code$	dt_delaydays	dt_datdelay:date9.	new_date:date9.;
format dt_datdelay date9.;
datalines ;	
106444375	133	16	21MAR2013:00:00:00.000	.
345347595	168	2	23DEC2019:00:00:00.000	.
323832873	168C	10	24FEB2020:00:00:00.000	.
178669982	122	30	06SEP2017:00:00:00.000	.
;							
run;

---

WRONG.

Code as posted, with PROC PRINT added:

Data delayed_accounts;				
infile cards expandtabs;				
input debt_code	rep_code$	dt_delaydays	dt_datdelay:date9.;
format dt_datdelay date9.;
datalines ;	
106444375	133	16	21MAR2013:00:00:00.000
345347595	168	2	23DEC2019:00:00:00.000
323832873	168C	10	24FEB2020:00:00:00.000
178669982	122	30	06SEP2017:00:00:00.000
;				
run;


Proc sql;
create table delayed_accounts_2 as
select *,
dt_datdelay + dt_delaydays as new_date format yymmdd10.
from delayed_accounts;
quit;

proc print data=delayed_accounts_2 noobs;
run;

Log:

 68         
 69         Data delayed_accounts;
 70         infile cards expandtabs;
 71         input debt_coderep_code$dt_delaydaysdt_datdelay:date9.;
 72         format dt_datdelay date9.;
 73         datalines ;
 
 NOTE: The data set WORK.DELAYED_ACCOUNTS has 4 observations and 4 variables.
 NOTE:  Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              670.00k
       OS Memory           19368.00k
       Timestamp           20.11.2023 03:30:09 nachm.
       Step Count                        24  Switch Count  2
       Page Faults                       0
       Page Reclaims                     171
       Page Swaps                        0
       Voluntary Context Switches        11
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 
 73       !            
 78         ;
 79         run;
 80         
 81         
 82         Proc sql;
 83         create table delayed_accounts_2 as
 84         select *,
 85         dt_datdelay + dt_delaydays as new_date format yymmdd10.
 86         from delayed_accounts;
 NOTE: Table WORK.DELAYED_ACCOUNTS_2 created, with 4 rows and 5 columns.
 
 87         quit;
 NOTE:  Verwendet wurde: PROZEDUR SQL - (Gesamtverarbeitungszeit):
       real time           0.00 seconds
       user cpu time       0.01 seconds
       system cpu time     0.00 seconds
       memory              5630.87k
       OS Memory           24748.00k
       Timestamp           20.11.2023 03:30:09 nachm.
       Step Count                        25  Switch Count  2
       Page Faults                       0
       Page Reclaims                     200
       Page Swaps                        0
       Voluntary Context Switches        12
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 
 88         
 89         proc print data=delayed_accounts_2 noobs;
 90         run;
 
 NOTE: There were 4 observations read from the data set WORK.DELAYED_ACCOUNTS_2.
 NOTE:  Verwendet wurde: PROZEDUR PRINT - (Gesamtverarbeitungszeit):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.01 seconds
       memory              1244.25k
       OS Memory           20648.00k
       Timestamp           20.11.2023 03:30:09 nachm.
       Step Count                        26  Switch Count  0
       Page Faults                       0
       Page Reclaims                     465
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           8

Result:

debt_code	rep_code	dt_delaydays	dt_datdelay	new_date
106444375	133	16	21MAR2013	2013-04-06
345347595	168	2	23DEC2019	2019-12-25
323832873	168C	10	24FEB2020	2020-03-05
178669982	122	30	06SEP2017	2017-10-06

As you can see, the result are non-missing values, cleanly formatted as dates.