BookmarkSubscribeRSS Feed
Ravikumarkummari
Quartz | Level 8

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.

15 REPLIES 15
Cynthia_sas
SAS Super FREQ


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

Ksharp
Super User

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

Cynthia_sas
SAS Super FREQ

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

Ravikumarkummari
Quartz | Level 8
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.

Ravikumarkummari
Quartz | Level 8

i am using sas 9.2 and msoffice version in 2010.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Ksharp
Super User

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

MarkNicholas
Obsidian | Level 7

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.

Cynthia_sas
SAS Super FREQ

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

Ravikumarkummari
Quartz | Level 8

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.

Cynthia_sas
SAS Super FREQ

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

Ravikumarkummari
Quartz | Level 8

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.

Ksharp
Super User

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;

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
  • 15 replies
  • 8951 views
  • 0 likes
  • 6 in conversation