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

[Multipla99]

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

[Tom]

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?

[Multipla99]

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

[Multipla99 · Accepted solution]

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;

[Multipla99]

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