DATA Step, Macro, Functions and more

how to convert a string variable so that it's exported in Excel as m/d/yyy h:mm (ex. 8/31/2018 8:15)

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

how to convert a string variable so that it's exported in Excel as m/d/yyy h:mm (ex. 8/31/2018 8:15)

HI All,

 

Each month, I receive some data in a CSV file that I import into SAS, create some new variables, and then export into Excel for distribution. The dates are formatted as: 2/16/2015 9:48. When I export them in Excel, I need them to look the same but, instead of being "General" I need them to be Excel date format m/d/yyy h:mm. Any suggestions?

 

Thanks!

Doctor J

Accepted Solutions
Solution
‎06-16-2017 02:34 PM
Super User
Super User
Posts: 7,942

Re: how to convert a string variable so that it's exported in Excel as m/d/yyy h:mm (ex. 8/31/2018 8

This depends very much on how you are exporting to Excel.  If you are ok to use ods tagsets.excelxp - which creates XML which can be read by Excel, then this is simply a matter of applying an excel tag in the proc report:

ods tagsets.excelxp file="want.xml";
proc report data=have nowd;
  columns col1;
  define col1 / "Label" style={tagattr='format:...'};
run;
ods tagsets.excelxp close;

Where I put the three dots (...) put the specific Excel format you need.

You can't get nice formatting with proc export or libname excel, these are basic data dumps.  You should however be able (if you have 9.4) to use ods excel to create native XLSX files with formatting.  Otherwise you could have some VBA code in the Excel file read the data and do whatever you need to do in Excel.

View solution in original post


All Replies
Solution
‎06-16-2017 02:34 PM
Super User
Super User
Posts: 7,942

Re: how to convert a string variable so that it's exported in Excel as m/d/yyy h:mm (ex. 8/31/2018 8

This depends very much on how you are exporting to Excel.  If you are ok to use ods tagsets.excelxp - which creates XML which can be read by Excel, then this is simply a matter of applying an excel tag in the proc report:

ods tagsets.excelxp file="want.xml";
proc report data=have nowd;
  columns col1;
  define col1 / "Label" style={tagattr='format:...'};
run;
ods tagsets.excelxp close;

Where I put the three dots (...) put the specific Excel format you need.

You can't get nice formatting with proc export or libname excel, these are basic data dumps.  You should however be able (if you have 9.4) to use ods excel to create native XLSX files with formatting.  Otherwise you could have some VBA code in the Excel file read the data and do whatever you need to do in Excel.

Contributor
Posts: 29

Re: how to convert a string variable so that it's exported in Excel as m/d/yyy h:mm (ex. 8/31/2018 8

Oh wow! Thank you! This is exactly what I was hoping for!!! I only started using tagsets.excelxp a week ago, so this is a great trick I knew nothing about! Thanks a million!

Doctor J
Contributor
Posts: 22

Re: how to convert a string variable so that it's exported in Excel as m/d/yyy h:mm (ex. 8/31/2018 8

If you can not apply an informat when reading the CSV file into SAS, and none of the available SAS DateTime formats are what you are looking for, try applying your own format.

 

See below for an example:

 

http://support.sas.com/kb/42/057.html

Contributor
Posts: 29

Re: how to convert a string variable so that it's exported in Excel as m/d/yyy h:mm (ex. 8/31/2018 8

Posted in reply to jdwaterman91

Thanks! When I import the CSV, I apply the character format to preserve all of the characters. Some of the departments and agencies I send the ouput to have their systems set up to read it in as the Excel date format. I've been trying to automate some of the tasks that my predecessor created as Excel files, and keep runnning into these issues. ONE day, it'll be easier and worth it. Cat Wink

Doctor J
Contributor
Posts: 29

Re: how to convert a string variable so that it's exported in Excel as m/d/yyy h:mm (ex. 8/31/2018 8

Posted in reply to jdwaterman91
Thanks!
Doctor J
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 296 views
  • 1 like
  • 3 in conversation