The SAS Output Delivery System and reporting techniques

Need to retain trailing period in excel output

Reply
New Contributor
Posts: 4

Need to retain trailing period in excel output

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;

Super User
Posts: 13,008

Re: Need to retain trailing period in excel output

" 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?

 

 

 

 

New Contributor
Posts: 4

Re: Need to retain trailing period in excel output

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.
Super User
Posts: 22,823

Re: Need to retain trailing period in excel output

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?

 

New Contributor
Posts: 4

Re: Need to retain trailing period in excel output

Do you mean using TAGATTR? Can you give me an example please, not really familiar with using a formula reference in this case.
Super User
Posts: 13,008

Re: Need to retain trailing period in excel output


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'.

 

 

New Contributor
Posts: 4

Re: Need to retain trailing period in excel output

Is there a way to make Excel keep it as a character value?
Super User
Posts: 22,823

Re: Need to retain trailing period in excel output

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

SAS Super FREQ
Posts: 343

Re: Need to retain trailing period in excel output

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

Ask a Question
Discussion stats
  • 8 replies
  • 235 views
  • 0 likes
  • 4 in conversation