Desktop productivity for business analysts and programmers

How to Retain Variable Format when Exporting to Excel

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

How to Retain Variable Format when Exporting to Excel

[ Edited ]

I am using Enterprise Guide to analyze a large payments data set. We have one field that contains a number to indicate the division responsible for a payment. I created a user-defined format to convert the number into the name of the division. Everything was working perfectly until I decided to export my results to Excel for a non-SAS user. That's when I realized my formats are only visible in Enterprise Guide. Unfortunately, the spreadsheet column contained only the division number and not its name. Did I make a mistake to use a format? Is there some way to quickly convert my formats to actual data in a new column that I can then export? (I'm trying to avoid manually listing each division number and name in a CASE statement since there are hundreds of division numbers.)

 

Thanks in advance!

 

 

 


Accepted Solutions
Solution
‎08-08-2017 10:05 AM
PROC Star
Posts: 1,167

Re: How to Retain Variable Format when Exporting to Excel

Posted in reply to brookeewhite1

If you use the "Send To" | "Microsoft Exel" option, it will keep the formatted values.

 

Tom

View solution in original post


All Replies
PROC Star
Posts: 7,474

Re: How to Retain Variable Format when Exporting to Excel

Posted in reply to brookeewhite1

I've never used it, as I don't use EG, but the following appears to have the capability you're looking for:

http://blogs.sas.com/content/sasdummy/2010/10/01/export-to-excel-20072010-using-sas-enterprise-guide...

 

Art, CEO, AnalystFinder.com

 

Contributor
Posts: 28

Re: How to Retain Variable Format when Exporting to Excel

Thank you! That does look helpful but unfortunately I'm not authorized to install anything additional on my work computer. 

Super User
Posts: 11,343

Re: How to Retain Variable Format when Exporting to Excel

Posted in reply to brookeewhite1

First, HOW did you export to Excel?

If you used proc export than that isn't going to do it easily. Proc Export is intended to interchange actual values.

 

You may want to look at an ODS output such as ODS Excel or Tagsets.Excelxp with a procedure such as Proc print which usually honors formats specified in the procedure.

Contributor
Posts: 28

Re: How to Retain Variable Format when Exporting to Excel

With my dataset open in Enterprise Guide, I clicked "Export" dropdown arrow then selected "Export WORK.QUERYXXXXXXXX." Next I specified my file location and name, and changed the File of Type dropdown from "SAS Data Files" to "Microsoft Excel workbooks". 

Solution
‎08-08-2017 10:05 AM
PROC Star
Posts: 1,167

Re: How to Retain Variable Format when Exporting to Excel

Posted in reply to brookeewhite1

If you use the "Send To" | "Microsoft Exel" option, it will keep the formatted values.

 

Tom

Contributor
Posts: 28

Re: How to Retain Variable Format when Exporting to Excel

It worked!  Thank you for the simple and easy fix!

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 220 views
  • 1 like
  • 4 in conversation