How to Retain Variable Format when Exporting to Excel

[jpagitt]

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.

[jpagitt]

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!

[DetBrik]

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. 

[jpagitt]

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.

[DetBrik]

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.