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 :
... View more
