topic Re: how to apply the format when export data into excel format is missing in ODS and Base Reporting

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

Ravikumarkummari — Sat, 11 Apr 2015 11:56:12 GMT

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.

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

Cynthia_sas — Sat, 11 Apr 2015 14:11:46 GMT

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

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

Ksharp — Sat, 11 Apr 2015 14:12:01 GMT

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

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

Cynthia_sas — Sat, 11 Apr 2015 18:20:19 GMT

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

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

Ravikumarkummari — Tue, 14 Apr 2015 09:04:23 GMT

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.

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

Ravikumarkummari — Tue, 14 Apr 2015 09:05:26 GMT

i am using sas 9.2 and msoffice version in 2010.

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

Ravikumarkummari — Tue, 14 Apr 2015 10:44:54 GMT

can anyone know the answer

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

RW9 — Tue, 14 Apr 2015 10:58:34 GMT

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.

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

MarkNicholas — Tue, 14 Apr 2015 11:08:15 GMT

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.

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

Kurt_Bremser — Tue, 14 Apr 2015 11:21:28 GMT

This seems to be the same as

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

Ksharp — Tue, 14 Apr 2015 12:22:49 GMT

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

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

Cynthia_sas — Tue, 14 Apr 2015 16:21:15 GMT

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

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

Ravikumarkummari — Wed, 15 Apr 2015 06:47:26 GMT

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.

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

Cynthia_sas — Thu, 16 Apr 2015 01:45:20 GMT

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

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

Ravikumarkummari — Tue, 21 Apr 2015 06:33:46 GMT

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.

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

Ksharp — Tue, 21 Apr 2015 12:09:44 GMT

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;