BookmarkSubscribeRSS Feed
Gieorgie
Quartz | Level 8

I would like to show in the final table that I export to Excel numbers that have 2 decimal places, not 6 or 7. I tried by

sum(a.EXP_PIERWOTNA_NETTO) as  EAD_PRE_CCF format _numeric_ 9.3,

but isn't work. What is the best solution in this case ?

12 REPLIES 12
acordes
Rhodochrosite | Level 12
format=9.2

The _numeric_ is an automatically created macro variable for all numeric variables, mostly used in an array statement. 

leave it and put a "=" between the format call and the specific format. 

put 9.2 if you want 2 decimal places 

Gieorgie
Quartz | Level 8
Hi, i did but still same nothing change sum(a.EXP_PIERWOTNA_NETTO) as EAD_PRE_CCF format = 9.2,
acordes
Rhodochrosite | Level 12

I suppose it's a snippet from a sql statement. Please show the complete code and verify that 

EXP_PIERWOTNA_NETTO

is a numeric variable 

acordes
Rhodochrosite | Level 12
proc sql;
select make, mean(horsepower) as avg_hp format=9.2 from 
sashelp.cars group by make;
quit;

that works

Kurt_Bremser
Super User

ALWAYS show us the whole log of your code when something does not work as expected; in your case, also include the code that transfers the data to Excel.

Gieorgie
Quartz | Level 8

Hi thanks for your answer

This is part my code with proc sgl:

proc sql;
    create table COREP_CR_&gv_tbl_date as
	select distinct
	a.DATA_DANYCH as REPORTING_DATE,
	"" as ID,
	    sum(a.EXP_PIERWOTNA_NETTO) as  EAD_PRE_CCF,
            sum(a.KOREKTA) as  PROVISION,
from corep
;
quit;

This is my code to export to excel :

proc export data=COREP_CR_&gv_tbl_date
            outfile="&glb_path2./reports/mth_ak/COREP_CR_&gv_tbl_date..xlsx"
            dbms=xlsx
            replace;
            sheet="corep_cr";
run; 
Kurt_Bremser
Super User

If adding

format=20.2

to your variables in the SQL step does not fix it, then the next step is using ODS EXCEL and a reporting procedure.

 

Note that your SQL might not create your intended result, as you calculate the total sums over the whole dataset and have these identical values in every combination of date/id. Did you miss to use a GROUP BY clause?

Gieorgie
Quartz | Level 8
I have a group, as I said, the code itself has 300 lines, so I did not put the ending sorry. So I understand I should add the format = 10.2 and then create the ODS EXCEL procedure?
Gieorgie
Quartz | Level 8

Ok, im trying to do like ODS EXCEL, when it generates the file it is created but empty

 

ods excel file = "&glb_path2./reports/mth_ak/COREP_CR_&gv_tbl_date..xlsx";  
proc print data=COREP_CR_&gv_tbl_date;

ods excel close;
Kurt_Bremser
Super User

@Gieorgie wrote:

Ok, im trying to do like ODS EXCEL, when it generates the file it is created but empty

 

ods excel file = "&glb_path2./reports/mth_ak/COREP_CR_&gv_tbl_date..xlsx";  
proc print data=COREP_CR_&gv_tbl_date;

ods excel close;

You have no RUN statement to create a step boundary, and global statements (like ODS) are executed immediately when encountered, so the Excel destination is closed before PROC PRINT writes any output to it.

See Maxim 49.

s_lassen
Meteorite | Level 14

I do not think Excel will show the number of decimals that are transferred from SAS - if you enter e.g. "1.00" in Excel it will just show a "1". You should probably select the column in Excel and set the number of decimals there ("Format Cells").

ballardw
Super User

@Gieorgie wrote:

I would like to show in the final table that I export to Excel numbers that have 2 decimal places, not 6 or 7. I tried by

sum(a.EXP_PIERWOTNA_NETTO) as  EAD_PRE_CCF format _numeric_ 9.3,

but isn't work. What is the best solution in this case ?


You really need to show exactly how you create Excel from SAS. Many approaches leave the display of resulting values to default behavior of Excel, which is extremely likely to not be what you want. For example, if you use Proc Export then values are passed to the result, no formatting of any type is provided from SAS and everything is open to Excel interpretation.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 12 replies
  • 6200 views
  • 3 likes
  • 5 in conversation