Changing the date from numeric to date format before exporting to Excel

Gieorgie — Wed, 22 Dec 2021 14:20:07 GMT

I have an end table, and I would like to export these results to Excel, however, after aggregation I changed the data from date format to numeric and when exporting to Excel it converts date to numbers. It is possible to change this value to a date before export

DATA2DATASTEP DSN FINAL_WYGASLE
DATA2DATASTEP FILE 5
DATA2DATASTEP FMT YES
DATA2DATASTEP FMTLIST policy_vintage VINTF.
DATA2DATASTEP INPUTLIST policy_vintage:VINTF. TODAY:32. PREV_MONTH:32. PREV_WEEK:32. DIFF_WEEK:32. DIFF_MONTH:32.
DATA2DATASTEP LBL YES
DATA2DATASTEP LIB WORK
DATA2DATASTEP MEMLABEL 
DATA2DATASTEP MSGTYPE NOTE
DATA2DATASTEP OBS MAX
DATA2DATASTEP OUTLIB WORK
DATA2DATASTEP VARLIST policy_vintage TODAY PREV_MONTH PREV_WEEK DIFF_WEEK DIFF_MONTH

Below is my code :

proc sql;
    create table diff_policy_exp as
		select
		 wyg.policy_vintage as policy_vintage
			 ,wyg.WYGASLE as TODAY
			 ,prior.WYGASLE as PREV_WEEK
			 ,wyg.WYGASLE - prior.WYGASLE as DIFF_WEEK
        from policy_vintage_weekly1 wyg
		        LEFT JOIN
             (select *
              from _work.policy_weekly
              where run_date < today() 
              having run_date = max(run_date)
             ) prior
        ON wyg.policy_vintage = prior.policy_vintage
;
quit;
proc sql;
create table diff_policy_exp as
select
t1.policy_vintage
,t1.TODAY
,t1.PREV_WEEK
,t1.DIFF_WEEK
,t2.PREV_MONTH
,t1.today - t2.prev_month as DIFF_MONTH
from diff_policy_exp t1
left join ( select policy_vintage, PREV_MONTH from policy_vintage_weekly_p1) t2
on t1.policy_vintage = t2.policy_vintage
;
quit;
/*tworzymy zmienną DATE_OLD do grupowania*/
%let date_old=%sysfunc(intnx(year,%sysfunc(Today()),-1,s));
proc format;
value vintf
  low-&date_old = 'OLD'
  other=[yymmd7.]
;
run;
/*agregujemy wyniki do daty vintf jako old*/
proc summary data=diff_policy_exp nway;
    class policy_vintage;
    format policy_vintage vintf.;
    var TODAY PREV_WEEK PREV_MONTH DIFF_WEEK DIFF_MONTH;
    output out=diff_policy_exp sum=;
proc sql;
create table final_wygasle as
select
policy_vintage
,TODAY 
,PREV_MONTH
,PREV_WEEK 
,DIFF_WEEK
,DIFF_MONTH
from diff_policy_exp
;
quit;

Here what i have in SAS :

Here what i got in Excel :

Re: Changing the date from numeric to date format before exporting to Excel

Kurt_Bremser — Wed, 22 Dec 2021 15:10:47 GMT

You won't be able to do this automatically, as your formatted value "OLD" can't be recognized by Excel as a date.

Change your SQL:

proc sql;
create table final_wygasle as
select
put(policy_vintage,vintf.) as policy_vintage
,TODAY 
,PREV_MONTH
,PREV_WEEK 
,DIFF_WEEK
,DIFF_MONTH
from diff_policy_exp
;
quit;

so that the data ends as character in Excel.

topic Changing the date from numeric to date format before exporting to Excel in SAS Programming

Changing the date from numeric to date format before exporting to Excel

Re: Changing the date from numeric to date format before exporting to Excel