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 :
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.
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.