SAS Programming

DATA Step, Macro, Functions and more
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.

sas-innovate-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 12 replies
  • 8458 views
  • 3 likes
  • 5 in conversation