BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
kjowers
Fluorite | Level 6

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
1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

kjowers
Fluorite | Level 6

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
jdwaterman91
Obsidian | Level 7

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

kjowers
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

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
  • 5 replies
  • 1007 views
  • 1 like
  • 3 in conversation