Help using Base SAS procedures

how to apply the format when export data into excel format is missing

Reply
Frequent Contributor
Posts: 110

how to apply the format when export data into excel format is missing

Good day all,

i have the dataset of emp data.

its having different formats for salary variable. comma and percent format.

so i  apply proc format for that.

PROC FORMAT;

VALUE FMT

  LOW-0,1-HIGH=[COMMA32.]

  OTHER=[PERCENT8.1]

  ;

RUN;

empno     salary

aaa          1,52,133

bbb          2,50,111

aaa          25%

bbb          50%.

in proc report its showing as it is. but while exporting it into excel only percentages format is applying. the comma format is missing.

after export into excel the output like this.

empno      salary

aaa     152133

bbb     250111

aaa     25%

bbb     50%

how to get the required output with applying comma and percentage format.

your valid comments are helps me a lot.

Thanks.

Super User
Posts: 6,927

Re: how to apply the format when export data into excel format is missing

Try to write "2,50,111" into a cell in Excel and see if Excel accepts that as a number. My bet is, it won't (maybe depending on locale).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 110

Re: how to apply the format when export data into excel format is missing

Instead of writing in excel while exporting only the format is there.

is it possible any sufficient method is there.

Frequent Contributor
Posts: 110

Re: how to apply the format when export data into excel format is missing

if there is two records then it is possible to manual entry. but when its contain thousands of records manual is not possible.

while exporting only the format is apply.

Super User
Posts: 6,927

Re: how to apply the format when export data into excel format is missing

What I meant is that Excel does not regard 1,52,133 as a number, but as text. SAS therefore sends the pure value without formatting, so that the number stays a number.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 6,927

Re: how to apply the format when export data into excel format is missing

BTW, if I use your format fmt. on the value 152133, it is rendered by SAS as 152,133, so I expect the locale value to play a big role in your problem.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 110

Re: how to apply the format when export data into excel format is missing

Can any One know the answer for this please comment.

Thanks in Advance.

Super User
Posts: 9,662

Re: how to apply the format when export data into excel format is missing

You need special style for excel .

Post it at ODS and Base Reporting  Cynthia will give you answer.

Ask a Question
Discussion stats
  • 7 replies
  • 277 views
  • 0 likes
  • 3 in conversation