BookmarkSubscribeRSS Feed
Gieorgie
Quartz | Level 8

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 :

Gieorgie_0-1640182767679.png

 

Here what i got in Excel :

Gieorgie_1-1640182794828.png

 

1 REPLY 1
Kurt_Bremser
Super User

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.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 644 views
  • 0 likes
  • 2 in conversation