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.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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