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;
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
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
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
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;
I dare to disagree (see screen shot below).
Did you go with Maxim2 and Maxim3 ?
Bart
@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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.