How can I change the format of the variables in the output of Proc Compare?

Multipla99 — Mon, 01 Sep 2025 14:03:20 GMT

Does anyone know how to get your output of Proc Compare formatted?

I'm running proc compare together with ods excel and my national version of excel uses decimal comma and a space for every third digit, instead of a decimal point and a comma between every third digit. Please see an example code below and some result rows further below. In the result rows you can see that I managed to get the decimal comma in the two columns Actual but I couldn't get rid of the point between every third digit. In the columns Diff and % Diff I didn't even get the decimal comma.

proc sort

data= sashelp.prdsal2

(keep= Country State ProdType Product Year Month Actual)

out= prdsal2

nodupkey;

by Country State ProdType Product Year Month;

run;

proc sort

data= sashelp.prdsal3

(keep= Country State ProdType Product Year Month Actual)

out= prdsal3

nodupkey;

by Country State ProdType Product Year Month;

run;

data prdsal2gem (rename= (Actual2= Actual))

prdsal3gem (rename= (Actual3= Actual));

merge prdsal2 (in= a rename= (Actual= Actual2))

prdsal3 (in= b rename= (Actual= Actual3));

by Country State ProdType Product Year Month ;

if a and b;

if a then output prdsal2gem;

if b then output prdsal3gem;

run;

ods excel file="\\C:\Temp\File.xlsx" style=statistical;

proc compare

base= prdsal2gem

compare= prdsal3gem

allstats

out= outstat;

id Country STATE ProdType Product Year Month;

var Actual;

format Actual commax10.2;

run;

ods excel close;

Value Comparison Results for Variables

_______________________________________________________________________________________________________________________

|| Actual Sales

|| Base Compare

COUNTRY STATE PRODTYPE PRODUCT YEAR MONTH || Actual Actual Diff. % Diff

__________ ____________________ __________ __________ ____ _____ || _________ _________ _________ _________

Canada British Columbia FURNITURE BED 1998 Janua || 1.522,00 1.674,20 152.2000 10.0000

Canada British Columbia FURNITURE BED 1998 Febru || 285,00 313,50 28.5000 10.0000

Canada British Columbia FURNITURE BED 1998 March || 502,00 552,20 50.2000 10.0000

Canada British Columbia FURNITURE BED 1998 April || 48,00 52,80 4.8000 10.0000

Re: How can I change the format of the variables in the output of Proc Compare?

Tom — Mon, 01 Sep 2025 15:02:24 GMT

I am not sure what you are saying is the issue.

Are you just talking about how SAS in general is displaying the variable ACTUAL? Or is it something specific to PROC COMPARE is the issue? Or perhaps to ODS EXCEL?

Is the variable named ACTUAL numeric or character? What display format do you have attached to it in your two input SAS dataset? Does it display the values the way you want?

Re: How can I change the format of the variables in the output of Proc Compare?

Multipla99 — Mon, 01 Sep 2025 16:36:18 GMT

Unfortunately I have missunderstood the commax format. What I'm looking for is found in the nlnum format and the locale of my country.

Re: How can I change the format of the variables in the output of Proc Compare?

Multipla99 — Mon, 01 Sep 2025 16:39:11 GMT

options locale=sv_SE;

ods excel file="\\C:\Temp\File.xlsx" style=statistical;

proc compare

base= prdsal2gem

compare= prdsal3gem

allstats

out= outstat;

id Country STATE ProdType Product Year Month;

var Actual;

format _numeric_ nlnum20.2;

run;

ods excel close;

Re: How can I change the format of the variables in the output of Proc Compare?

Multipla99 — Mon, 01 Sep 2025 16:40:34 GMT

Dear Tom, I appologize for the messy post. Thank you for trying to help!

topic Re: How can I change the format of the variables in the output of Proc Compare? in SAS Procedures

How can I change the format of the variables in the output of Proc Compare?

Re: How can I change the format of the variables in the output of Proc Compare?

Re: How can I change the format of the variables in the output of Proc Compare?

Re: How can I change the format of the variables in the output of Proc Compare?

Re: How can I change the format of the variables in the output of Proc Compare?