DATA Step, Macro, Functions and more

How to Retain Variable Format when Exporting to Excel

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

How to Retain Variable Format when Exporting to Excel

Good morning, all:

 

I am having trouble getting my code to retain its format when exporting to excel. I am using ODS EXCEL instead of something like PROC EXPORT so that I can retain the percentages in my data, but I have two fields that I used a format and then a subsequent data step to get into the desired order and when I export to Excel it gives me the numeric values instead of the alpha. Below is the last portion of my code where this is happening.

 

 

proc format;
value $GreekFlag
    1='Greek'
    2='No Greek Affiliation';
value $ClassLevel
    1='FR'
    2='SO'
    3='JR'
    4='SR'
    5='UGRD2'
    6='GR';
run;



data enrollment_formatted;
    set enrollment_aggregated;
    if greek_nongreek='Greek' then greek_nongreek='1';
        else if greek_nongreek='No Greek Affiliation' then greek_nongreek='2';
    if class_level='FR' then class_level='1';
        else if class_level='SO' then class_level='2';
        else if class_level='JR' then class_level='3';
        else if class_level='SR' then class_level='4';
        else if class_level='UGRD2' then class_level='5';
        else if class_level='GR' then class_level='6';
run;



proc sort data=enrollment_formatted;
    by greek_nongreek class_level;
run;



data enrollment_final;
    set enrollment_formatted;
    format greek_nongreek $greekflag.;
    format class_level $classlevel.;
run;

 

 

ods excel file="\\filedestination.xlsx";
proc report data=enrollment_final;
    columns _all_;
run;
ods excel close;

 

 

 

Current output of the first two columns in excel:

 

Greek_NonGreekClass_Level
11
12
13
14
16
21
22
23
24
25
26

 

 

The desired output in the first two columns (and what is shown as my dataset in SAS):

 

Greek_NonGreekClass_Level
GreekFR
GreekSO
GreekJR
GreekSR
GreekGR
No Greek AfilliationFR
No Greek AfilliationSO
No Greek AfilliationJR
No Greek AfilliationSR
No Greek AfilliationUGRD2
No Greek AfilliationGR

 

 

 

I have seen some similar solutions, but nothing really touching on this. If you know of a paper/post that addresses this issue, a link to it would be much appreciated.

 

Thank you.


Accepted Solutions
Solution
‎06-27-2017 03:26 PM
Super User
Posts: 17,775

Re: How to Retain Variable Format when Exporting to Excel

ODS Excel is not production ready until SAS 9.4 TS1M3. Before that it was still in testing and there are bugs...

View solution in original post


All Replies
Super User
Posts: 17,775

Re: How to Retain Variable Format when Exporting to Excel

What version of SAS do you have? I would have expected that to be the default behaviour.

Occasional Contributor
Posts: 14

Re: How to Retain Variable Format when Exporting to Excel

Sorry - I meant to include that in the post. SAS 9.4

Super User
Posts: 17,775

Re: How to Retain Variable Format when Exporting to Excel

Your code has other errors.

I tried this with a proc print and it worked ok.

I suspect your formats are incorrect - they're defined as character for one, when you've actually defined numeric data.

 

So...I would check the rest of your code for other errors and try proc print, or writing out the full proc report.

 

The issue you're having is likely identified in your log via a warning or error.

 

proc format;
value GreekFlag
    1='Greek'
    2='No Greek Affiliation';
value ClassLevel
    1='FR'
    2='SO'
    3='JR'
    4='SR'
    5='UGRD2'
    6='GR';
run;

data random;
do i=1 to 2;
do j=1 to 6;
output;
end;
end;


    format i greekflag.;
    format j classlevel.;

run;


ods excel file="c:\_localdata\temp\sample.xlsx";
proc print data=random;
run;
ods excel close;
 

Results:

 

Obs i j
1 Greek FR
2 Greek SO
3 Greek JR
4 Greek SR
5 Greek UGRD2
6 Greek GR
7 No Greek Affiliation FR
8 No Greek Affiliation SO
9 No Greek Affiliation JR
10 No Greek Affiliation SR
11 No Greek Affiliation UGRD2
12 No Greek Affiliation GR

 

Contributor
Posts: 22

Re: How to Retain Variable Format when Exporting to Excel

I was unable to reproduce the error, so it must depend on an environmental condition. In my opinion, the formats should work as coded, but I also tried them in this form, with the same results:

 

proc format;
value $GreekFlag
    '1'='Greek'
    '2'='No Greek Affiliation';
value $ClassLevel
    '1'='FR'
    '2'='SO'
    '3'='JR'
    '4'='SR'
    '5'='UGRD2'
    '6'='GR';
run;

 

In a case like this, I would check for leading spaces in the character codes. Leading spaces won't always be displayed, but they can interfere with the working of a character value format. However, I don't see anything in the logic that could have created leading spaces.

Occasional Contributor
Posts: 14

Re: How to Retain Variable Format when Exporting to Excel

Thanks for the help so far you all. As of now the code is still producing the incorrect output. There is no error according to the log either.

 

I have updated the code to reflect the information given thus far: changed the numeric format to character formats in the data step and put single quotes around the numbers in the proc format step, but it does not apper to have actually changed anything. There are no leading zeros in the data from what I can tell. It might be worth mentioning that the values that are listed in the two columns (Greek, No Greek Affiliation) & (FR, SO, JR, etc.) are not directly from the tables, but are custom values based on critera that I have defined. I don't know if that should make a difference or not, but I can post the beginning of the code if necessary.

Super User
Posts: 17,775

Re: How to Retain Variable Format when Exporting to Excel

1. Post a snapshot of what your variables look like before proc report.

2. What happens when you use proc print, instead of proc report?

Occasional Contributor
Posts: 14

Re: How to Retain Variable Format when Exporting to Excel

1. I feel like the code that I am using to produce the two fields might be more beneficial than a view at the tables. Here is the code:

 

proc sql;
create table Enrollment_base as
select distinct a.report_term, a.emplid, a.classification, a.acad_prog,
       case when a.emplid=b.emplid then "Greek" else "No Greek Affiliation" end as greek_flag,
       case when a.emplid=c.emplid then 1 else 0 end as returning_flag,
       case when a.emplid=d.emplid then 1 else 0 end as graduated_flag,
       case when a.classification='1' and a.acad_prog in ('UGRD','UGNDS') then 'FR'
       when a.classification='2' and a.acad_prog in ('UGRD','UGNDS') then 'SO'
       when a.classification='3' and a.acad_prog in ('UGRD','UGNDS') then 'JR'
       when a.classification='4' and a.acad_prog in ('UGRD','UGNDS') then 'SR'
       when a.acad_prog='UGRD2' then 'UGRD2'
       when a.classification in ('5', '6', '7', '8') then 'GR' end as Class_Level
from sqlserv.cbm001_augmented a
left join greek_population b
on a.emplid=b.emplid
left join returned c
on a.emplid=c.emplid
left join graduated d
on a.emplid=d.emplid
where a.report_term="&enrollterm";
quit;

 

 

2. I am pretty new to SAS and coding in general, so I may not really understand your question, but I am not using proc print or proc report - at least I am not using those specific statements. My code is entirely made up of proc sql and data statements.

Super User
Posts: 17,775

Re: How to Retain Variable Format when Exporting to Excel

[ Edited ]

jpagitt wrote:

1. I feel like the code that I am using to produce the two fields might be more beneficial than a view at the tables. Here is the code:

 


No, please post a proc contents of the dataset you're trying to export. We don't need to debug your entire code.

proc contents data=enrollment_final;
run;

jpagitt wrote:

 

 

2. I am pretty new to SAS and coding in general, so I may not really understand your question, but I am not using proc print or proc report - at least I am not using those specific statements. My code is entirely made up of proc sql and data statements.


Please review the code you originally posted. There is a PROC REPORT in between the ODS EXCEL statements. I'm saying switch that to a PROC PRINT.

 

proc print data=enrollment_final noobs label;
run;

 

Occasional Contributor
Posts: 14

Re: How to Retain Variable Format when Exporting to Excel

Thanks for clearing that up for me. Below is a screenshot of the variables and attributes.

 

Greek_Variables.JPG

 

Using PROC PRINT is as expected - it looks just like the SAS dataset when I look at it in the explorer.


Greek_Variables.JPG
Super User
Posts: 17,775

Re: How to Retain Variable Format when Exporting to Excel

Are your formats applied correctly in the dataset explorer or in the PROC PRINT output?

Occasional Contributor
Posts: 14

Re: How to Retain Variable Format when Exporting to Excel

Yes they are. It is only when I use ODS EXCEL to output to excel that I lose the format and the data uses the numeric values instead of the alpha values.

Super User
Posts: 17,775

Re: How to Retain Variable Format when Exporting to Excel

What happens when you run the example I originally posted?

 

What exact versio of SAS are you using? Ie SAS 9.4 TS1M4?

Occasional Contributor
Posts: 14

Re: How to Retain Variable Format when Exporting to Excel

When I run the example it does output with correct formats in excel, so that is good to know.

 

Exact version is SAS 9.4 TS1M1

Solution
‎06-27-2017 03:26 PM
Super User
Posts: 17,775

Re: How to Retain Variable Format when Exporting to Excel

ODS Excel is not production ready until SAS 9.4 TS1M3. Before that it was still in testing and there are bugs...

☑ This topic is SOLVED.

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

Discussion stats
  • 19 replies
  • 257 views
  • 1 like
  • 4 in conversation