BookmarkSubscribeRSS Feed
ajo1
Calcite | Level 5

I'm using ODS excel to export data to an excel document for a project. Because the spreadsheet must contain exactly what is in the dataset, including leading blanks, I have to use ODS excel instead of something else like proc export. Some of the observations have a trailing period at the end, for example something like '1234.'. The period shows up in the results window but isn't showing up in the excel file. Is there any way I can retain this period. I'm using the following code. This is in Enterprise Guide 7.1 if that makes a difference.

 

ods excel file="&lib.test.xlsx"
options(sheet_interval='none' suppress_bylines='no'
absolute_column_width='40,40,40,25,25,25,25,25,25,25,25,25,25,25' absolute_row_height='15')
;

proc report data=set1
nowd style(header)={color=black font=("Times New Roman" , 11pt) width=100% background=_undef_}
style(column)={cellheight=.5in font=("Times New Roman", 11pt)};
column &names.;
define response /display style(column)={asis=on width=1000%};
run;

ods excel close;

8 REPLIES 8
ballardw
Super User

" Some of the observations have a trailing period at the end, for example something like '1234.'. "

 

Is that a SAS variable? What format does the variable have?

 

 

 

 

ajo1
Calcite | Level 5
It's a character variable since some of the observations are strings instead of just numbers.
I also noticed that if an observation starts with a period, the excel file will add a 0 before it. I'm assuming it's trying to format it as a number instead of a character, but I need it exactly as it is in the sas dataset.
Reeza
Super User

Not ideal but can you use the formula reference for that field instead?

Then add the ' in front which tells Excel to treat it as a character?

 

ajo1
Calcite | Level 5
Do you mean using TAGATTR? Can you give me an example please, not really familiar with using a formula reference in this case.
ballardw
Super User

@ajo1 wrote:
It's a character variable since some of the observations are strings instead of just numbers.
I also noticed that if an observation starts with a period, the excel file will add a 0 before it. I'm assuming it's trying to format it as a number instead of a character, but I need it exactly as it is in the sas dataset.

I'm afraid that you really will need more than setting the appearance in Excel as Excel has changed the value from character to numeric. So you no longer have data 'exactly as in the dataset'.

 

 

ajo1
Calcite | Level 5
Is there a way to make Excel keep it as a character value?
Reeza
Super User

TAGATTR

 


@ajo1 wrote:
Is there a way to make Excel keep it as a character value?

https://www.sas.com/content/dam/SAS/support/en/technical-papers/SAS5642-2016.pdf

Vince_SAS
Rhodochrosite | Level 12

Is a SAS character format applied to the column?  If not, then try applying one, as shown for the string2 column in this code:

 

ods _all_ close;

data work.test;
length string1 string2 $20;
infile cards;
input string1;
string2 = string1;
cards;
.1234
0.1234
1234.
;
run;

ods Excel file='C:\temp\temp.xlsx' style=HTMLBlue;

proc report data=work.test;
  column string1 string2;
  define string2 / f=$20.;
run; quit;

ods Excel close;

 

Vince DelGobbo

SAS R&D

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
  • 8 replies
  • 1047 views
  • 0 likes
  • 4 in conversation