topic Re: How to Export to Excel while keeping formats? in SAS Programming

How to Export to Excel while keeping formats?

FrustratedBio — Fri, 16 Jul 2021 20:46:34 GMT

Here is my code

data ForKatie;
set floridam;
format Department_Name $Deptfmt.;
run;

Here is my file path:

"/home/u58028011/sasuser.v94/ForKatie";

I've tried using ODS and Proc Export, but it doesn't seem to like either of those. Are there any quick ways any of y'all can help me with?

Re: How to Export to Excel while keeping formats?

Rydhm — Fri, 16 Jul 2021 21:16:10 GMT

But ODS Excel should run fine. See example below:

proc format;
value $num
 '1'="One"
 '2'="Two"
 ;
run;

data test;
 today=today();
 format today date9.;
 number='1';
 format number $num.;
run;


ods excel file="H:\output_temp\test.xlsx" ;
proc report data=test;
  columns _all_;
run;
ods excel close;

Re: How to Export to Excel while keeping formats?

ballardw — Fri, 16 Jul 2021 21:32:13 GMT

@FrustratedBio wrote:

Here is my code

data ForKatie;
set floridam;
format Department_Name $Deptfmt.;
run;

Here is my file path:

"/home/u58028011/sasuser.v94/ForKatie";

I've tried using ODS and Proc Export, but it doesn't seem to like either of those. Are there any quick ways any of y'all can help me with?

"Doesn't seem to like" is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "<>" to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "</>" icon or attached as text to show exactly what you have and that we can test code against.

At least show the code attempted.

Better would be to provide data with the "formats" that Excel doesn't like with the definition of the format $deptfmt..

Re: How to Export to Excel while keeping formats?

Ksharp — Sat, 17 Jul 2021 10:52:32 GMT

Check 'tagattr=' style.

ods excel file='c:\temp\temp.xlsx';
proc report data=sashelp.class nowd;
define weight/display style={tagattr='format:@ type:string'};
run;
ods excel close;

Re: How to Export to Excel while keeping formats?

FrustratedBio — Mon, 19 Jul 2021 19:13:27 GMT

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
NOTE: ODS statements in the SAS Studio environment may disable some output features.
73
74 ods excel file='c:/home/u58028011/sasuser.v94/010120-052621_COVID Pandemic Associate Events_ViaOne.xlsx';
75 proc report data=FloridaM nowd;
76 define weight/display style={tagattr='format:@ type:string'};
77 run;

WARNING: weight is not in the report definition.
NOTE: There were 1107 observations read from the data set WORK.FLORIDAM.
NOTE: PROCEDURE REPORT used (Total process time):
real time 18.01 seconds
user cpu time 17.84 seconds
system cpu time 0.17 seconds
memory 170427.92k
OS Memory 231644.00k
Timestamp 07/19/2021 07:12:03 PM
Step Count 74 Switch Count 1
Page Faults 0
Page Reclaims 45061
Page Swaps 0
Voluntary Context Switches 30
Involuntary Context Switches 22
Block Input Operations 0
Block Output Operations 5952

78 ods excel close;
ERROR: Physical file does not exist, /pbr/biconfig/940/Lev1/SASApp/c:/home/u58028011/sasuser.v94/010120-052621_COVID Pandemic
Associate Events_ViaOne.xlsx.
79
80 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
92

These are the notes

This is the attempted code

ods excel file='c:/home/u58028011/sasuser.v94/010120-052621_COVID Pandemic Associate Events_ViaOne.xlsx';
proc report data=FloridaM nowd;
define weight/display style={tagattr='format:@ type:string'};
run;
ods excel close;

Thanks!

Re: How to Export to Excel while keeping formats?

FrustratedBio — Mon, 19 Jul 2021 19:15:21 GMT

It went through, but not all the way.....see reply below with code and log.

Re: How to Export to Excel while keeping formats?

Ksharp — Tue, 20 Jul 2021 12:05:22 GMT

I just gave you an example . You can find other format in Excel:

And also could find out more example about 'tagattrs' in support.sas.com .

Or Calling @Cynthia_sas , she have more experience about this style .

Re: How to Export to Excel while keeping formats?

Cynthia_sas — Tue, 20 Jul 2021 15:38:53 GMT

Hi:

One of your issues is with the value for the FILE= option in your ODS statement:

The C: part of the path is highlighted in pink because SAS OnDemand for Academics is a Linux/Unix box and does NOT have a C: drive. So this reference to C: will generate an error, as you showed:

So, when the OnDemand server went to look for c:/home... location, it didn't find it and so it looked in the default working folder on the server, didn't find it and issued an error message.

If you are using SAS OnDemand for Academics, you should always start your paths with /home or else with a ~ (tilde). For example, if I wanted to create an ODS result file for ODS EXCEL on my SAS OnDemand account, I would write the output to my RESULTS folder (that I created under Files (Home) top node. And so my ODS EXCEL statement would look like this:

ods excel file='/home/<myuserID>/RESULTS/example1.xlsx';
or
ods excel file='~/RESULTS/example2.xlsx';

My tendency is not to write files to my SASUSER folder on the OnDemand server because I prefer to create my own folders under Files (Home) top node in which to store my output. Under my Files (Home) location, I have a folder for Programming 1 (EPG1V2), folders for other classes, a folder for PROGRAMS, and a folder for RESULTS.

You can always find the correct path to a folder on the OnDemand server, by clicking on Server Files and Folders, then clicking to expand the Files (Home) top node. Make a folder to hold your results. Maybe call it RESULTS. Right click on that folder and choose Properties. The Location field in the Properties window will show you the path you need to use for that folder. For a FILE= option, you'd only need to add your filename and extension to the back end of the path from the Properties window.

You are getting a WARNING on the WEIGHT variable because you don't have a COLUMN statement in PROC REPORT. It's generally a best practice to use a COLUMN statement so you can control the order of the variables and statistics that you want to place on the report.

Until you have a working program, you will not be able to tell whether your format is working or not. I notice that you are trying to format WEIGHT as a string. Just remember that Excel can be very picky about numbers formatted as text, so even if you use TAGATTR, you may still see the "number stored as text" warning when you open the result file in Excel.

This user group paper https://support.sas.com/resources/papers/proceedings11/266-2011.pdf discussed the use of TAGATTR in the context of TAGSETS.EXCELXP, but the concepts also apply to ODS EXCEL. You will definitely want to use the TAGATTR if you want to send a Microsoft format for your number from SAS to Excel.

Cynthia