Show result numbers with two decimal places

[Gieorgie]

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 ?

[acordes]

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]

Hi, i did but still same nothing change sum(a.EXP_PIERWOTNA_NETTO) as EAD_PRE_CCF format = 9.2,

[acordes]

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]

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

that works

[Kurt_Bremser]

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]

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]

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]

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]

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]

---

@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]

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]

---

@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.