The SAS Output Delivery System and reporting techniques

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

Reply
Frequent Contributor
Posts: 111

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.

SAS Super FREQ
Posts: 8,868

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

Posted in reply to Ravikumarkummari


Hi:

  Your posted data doesn't make sense to me. You say that these are the values for SALARY: 1,52,133 or 2,50,111 -- but why do you have commas in inappropriate places? Usually the commas stand as the THOUSANDS separator -- this is what COMMA31 will give you. So if you want 1,52,133, you will NOT get that from COMMA32 -- what you would get for 152133 is 152,133 and what you would get for 1234567 is  1,234,567; and what you would get  for 123456789 is 123,456,789; and what you would get for 987654321123456789 is 987,654,321,123,456,789 so your representation of the data doesn't make sense. Second, even through EMPNO is fake, do you really have non-unique EMPNO values? Is this pre-summarized data? Do you have one ROW with a value AAA and then a second row with EXACTLY the same value of AAA? That doesn't make sense either.

  Finally, what PROC REPORT code have you tried? What is your destination of interest? How are you making your file for Excel? Excel generally ignores any SAS formats that you send to it -- it uses a default column width and default "General" format for numbers. Excel doesn't like numbers stored as text and so without seeing some code, including your ODS statements, it is hard to comment in a constructive way.

cynthia

Super User
Posts: 10,046

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

Posted in reply to Ravikumarkummari

Very funny. Even I change it into character variable. Excel is still considering it as a numeric value . Therefore ,I have to put a TAB character before it to make it as character forcedly .

PROC FORMAT;
VALUE FMT
  LOW-0,1-HIGH=[COMMA32.2]
  OTHER=[PERCENT8.1]
  ;
RUN;

  data have;
input  value;
cards;
0.5
0.8
4323
232134.87
4563454.8
457456.9
4642348.7
453439.8
;
run;

ods listing close;
ods tagsets.excelxp file='c:\temp\z.xls' style=sasweb;
proc report data=have nowd;
column value v;
define value/display noprint;
define v/computed ;
compute v/character length=30;
 v='09'x||put(value,fmt18. -l) ;
endcomp;
run;
ods tagsets.excelxp close;
ods listing;

Xia Keshan

SAS Super FREQ
Posts: 8,868

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

Hi:

  The user-defined format will work for other ODS destinations, like RTF, PDF and HTML, but will rarely work for Excel. Instead of making a character variable or padding with a tab character, the OP could use CALL DEFINE. But without knowing what destination is being used to get the output into Excel -- could be CSV, could be HTML, could be XML -- there's no way to show the proper CALL DEFINE code. It would be useful for the OP to post some sample code and explain the anomalies in the comma format for the numbers that were posted.

cynthia

Frequent Contributor
Posts: 111

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

Posted in reply to Cynthia_sas
Jan-15
4,310,017,319
33,339,23,219
25,413,78,417
3.50%
4.50%
8.70%

this what i have up to mar-15. and initially the variable jan15 is in character format. for calculations i converted it into fmt32. format.

I applied proc format like this.

PROC FORMAT;

VALUE FMT

1-HIGH=[COMMA32.]

LOW-0.9999=[PERCENT12.1]

;

RUN;

now the variable jan15 is in numeric format.

after applying

ods tagsets.excelxp file="D:\sample.xls" ;

proc report data=emp nowd;

run;

ods tagsets.excelxp close;

i used this code for exporting. but what happened is that in excel file only percentage format is appearing but comma format is missing.

can you help me madam to solve this problem.

Frequent Contributor
Posts: 111

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

Posted in reply to Ravikumarkummari

i am using sas 9.2 and msoffice version in 2010.

Frequent Contributor
Posts: 111

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

Posted in reply to Ravikumarkummari

can anyone know the answer

Super User
Super User
Posts: 7,997

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

Posted in reply to Ravikumarkummari

As your using ods tagsets.excelxp then you will want a style with tagattr options:

proc report...;

     define your_column / style={tagattr="format:#,##0.00"};

...

However looking through your text, it seems you want different formats on different cells.  I don't think you can do this directly.  SAS is a structured labguage, i.e. a column only has one format applied to all values.  SAS != Excel.  You could post process the data, or convert the numeric to text in the format you want.  Then put the tagattr as text to avoid Excel converting it to number again.

Super User
Posts: 10,046

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

Posted in reply to Ravikumarkummari

Did you try my code ? force it to be a character value by adding a TAB before it .

Contributor
Posts: 31

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

Posted in reply to Ravikumarkummari

Hi:

First we need to understand that proc format only helps to "see your data" as per your expectation - it happens in proc export.

However, only the true values are exported to excel.  To explicitly apply formats in excel, you need to use tagattr only.

Also, since your column format is a custom in nature, you may need to prepare a new format and apply to it.

SAS Super FREQ
Posts: 8,868

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

Posted in reply to Ravikumarkummari

Hi:

But in your original posting, you show TWO variables: EMPNO and SALARY. Yet, in your PROC REPORT code, you do not show any COLUMN statement or show where you use the format you've created. You describe the data as having "Jan-15", but in your original data, you did not have a DATE variable. Can you post some sample data and ALL of your PROC REPORT code that shows how you are using the format. Here is a simple example using SASHELP.CLASS -- I know this is not structured like your data, but since you have described the data differently in several postings, it is impossible to understand what your data looks like. But this REPORT examples does show the only way to illustrate using different formats with TAGSETS.EXCELXP.

cynthia

diff_formats_same_col.png

Frequent Contributor
Posts: 111

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

Posted in reply to Cynthia_sas

i seen your image the problem was in this image the numeric variable are in the format comma8.1 ,comma8.2, comma8.3

but in my dataset its having proc format i.e. fmt32.

in that variable both comma32. and percent12.1 are available.

So when using this image code its not working.

i think you have seen my proc format above.

i want to export it into excel.

So could you please help me to export the whole variable into in a single with comma and percentage format.

it will be great helpful for me anybody to give comments.

SAS Super FREQ
Posts: 8,868

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

Posted in reply to Ravikumarkummari

Hi:

  And, as I indicated in  a previous posting: "The user-defined format will work for other ODS destinations, like RTF, PDF and HTML, but will rarely work for Excel." So the CALL DEFINE method is the one you will have to use to get Excel to respect any of your formats. I just included the formats for comma8.1, comma8.2 and comma8.3 to illustrate how you would change them. You could certainly make them comma32. instead.

cynthia

Frequent Contributor
Posts: 111

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

Posted in reply to Cynthia_sas

I followed your procedure Cythia madam, the code is below.

ODS TAGSETS.EXCELXP FILE="D:\OUTPUT.XLS" ;

PROC REPORT DATA=WORK.EMP NOWD;

DEFINE  DEC14 / SUM FORMAT= FMT32. WIDTH=32    SPACING=2   RIGHT "DEC-14" ;

COMPUTE DEC14;

IF X IN(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20)  THEN DO;

CALL DEFINE (_COL_,'STYLE', 'STYLE={TAGATTR="FORMAT:#,###,###"}');

END;

IF X IN(21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40) THEN DO;

CALL DEFINE (_COL_,'STYLE', 'STYLE={TAGATTR="FORMAT:###.0\%"}');

END;

ENDCOMP;

ODS TAGSETS,EXCELXP CLOSE;

I CREATED X VARIABLE FOR ROWNUMBER IN DATASTEP.

X=_N_;

But in output result only percent format is applying and comma format is missing.

Super User
Posts: 10,046

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

Posted in reply to Ravikumarkummari

Maybe for your purpose , should like :

IF X>1 or X<0 THEN DO;

CALL DEFINE (_COL_,'STYLE', 'STYLE={TAGATTR="FORMAT:#,###,###"}');

END;

IF 0<=X<=1 THEN DO;

CALL DEFINE (_COL_,'STYLE', 'STYLE={TAGATTR="FORMAT:###.0\%"}');

END;

Ask a Question
Discussion stats
  • 15 replies
  • 4920 views
  • 0 likes
  • 6 in conversation