topic Re: How to Retain Variable Format when Exporting to Excel in SAS Programming

How to Retain Variable Format when Exporting to Excel

jpagitt — Fri, 23 Jun 2017 16:06:02 GMT

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_NonGreek	Class_Level
1	1
1	2
1	3
1	4
1	6
2	1
2	2
2	3
2	4
2	5
2	6

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

Greek_NonGreek	Class_Level
Greek	FR
Greek	SO
Greek	JR
Greek	SR
Greek	GR
No Greek Afilliation	FR
No Greek Afilliation	SO
No Greek Afilliation	JR
No Greek Afilliation	SR
No Greek Afilliation	UGRD2
No Greek Afilliation	GR

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.

Re: How to Retain Variable Format when Exporting to Excel

Reeza — Fri, 23 Jun 2017 17:19:45 GMT

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

Re: How to Retain Variable Format when Exporting to Excel

jpagitt — Fri, 23 Jun 2017 17:22:28 GMT

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

Re: How to Retain Variable Format when Exporting to Excel

Reeza — Fri, 23 Jun 2017 17:29:41 GMT

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

Re: How to Retain Variable Format when Exporting to Excel

RickAster — Fri, 23 Jun 2017 18:02:02 GMT

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.

Re: How to Retain Variable Format when Exporting to Excel

jpagitt — Fri, 23 Jun 2017 18:12:31 GMT

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.

Re: How to Retain Variable Format when Exporting to Excel

Reeza — Fri, 23 Jun 2017 18:52:50 GMT

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?

Re: How to Retain Variable Format when Exporting to Excel

jpagitt — Fri, 23 Jun 2017 19:23:03 GMT

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.

Re: How to Retain Variable Format when Exporting to Excel

Reeza — Fri, 23 Jun 2017 19:27:54 GMT

@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;

Re: How to Retain Variable Format when Exporting to Excel

jpagitt — Fri, 23 Jun 2017 19:46:33 GMT

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

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

Re: How to Retain Variable Format when Exporting to Excel

Reeza — Fri, 23 Jun 2017 19:55:35 GMT

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

Re: How to Retain Variable Format when Exporting to Excel

jpagitt — Fri, 23 Jun 2017 19:58:54 GMT

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.

Re: How to Retain Variable Format when Exporting to Excel

DetBrik — Fri, 23 Jun 2017 20:24:04 GMT

I ran your code exactly as written using the "Desired Output" table as input to create the "enrollment_aggregated" dataset (after correctling the spelling of the word "Affiliation") and the program produced the desired output in Excel(R). My spreadsheet contained the formatted values of the variables. Thus, there must be some other problem(s) in your code Reeza suggested.

Re: How to Retain Variable Format when Exporting to Excel

jpagitt — Fri, 23 Jun 2017 20:28:53 GMT

Do you mind posting the sample code that you used? Maybe there is something that you did slightly differently that I can glean from it. The log gives me no errors whatsoever, so finding it has proven quite difficult.

Re: How to Retain Variable Format when Exporting to Excel

Reeza — Fri, 23 Jun 2017 20:31:50 GMT

What happens when you run the example I originally posted?

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

Re: How to Retain Variable Format when Exporting to Excel

jpagitt — Fri, 23 Jun 2017 20:36:39 GMT

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

Re: How to Retain Variable Format when Exporting to Excel

Reeza — Fri, 23 Jun 2017 20:40:20 GMT

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

Re: How to Retain Variable Format when Exporting to Excel

DetBrik — Fri, 23 Jun 2017 20:41:14 GMT

Here is the code I ran that produced the desired results. I am using SAS 9.4 (TS1M3).

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_aggregated;
input Greek_NonGreek $20. Class_Level $5.;
datalines;
Greek               FR 
Greek               SO 
Greek               JR 
Greek               SR 
Greek               GR 
No Greek AffiliationFR 
No Greek AffiliationSO 	
No Greek AffiliationJR 
No Greek AffiliationSR 
No Greek AffiliationUGRD2 
No Greek AffiliationGR 
;;;;
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="C:\_LocalPath\My SAS Files\9.4\Greek.xlsx";
proc report data=enrollment_final;
    columns _all_;
run;
ods excel close;

HTH.

Re: How to Retain Variable Format when Exporting to Excel

jpagitt — Fri, 23 Jun 2017 20:47:56 GMT

Well, hopefully that explains it! I have a means of updating. I am going to reinstall with the correct version and then I will see if it works and post back here.

Re: How to Retain Variable Format when Exporting to Excel

jpagitt — Tue, 27 Jun 2017 19:29:40 GMT

I wanted to add that I updated SAS to 9.4 TS1M3 and my code works as expected. Turns out that is all I needed.

Thank you for the help!