BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jpagitt
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

19 REPLIES 19
Reeza
Super User

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

jpagitt
Obsidian | Level 7

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

Reeza
Super User

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

 

RickAster
Obsidian | Level 7

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.

jpagitt
Obsidian | Level 7

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.

Reeza
Super User

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?

jpagitt
Obsidian | Level 7

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.

Reeza
Super User

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

 

jpagitt
Obsidian | Level 7

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
Reeza
Super User

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

jpagitt
Obsidian | Level 7

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.

Reeza
Super User

What happens when you run the example I originally posted?

 

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

jpagitt
Obsidian | Level 7

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

Reeza
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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