BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Multipla99
Quartz | Level 8
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        
1 ACCEPTED SOLUTION

Accepted Solutions
Multipla99
Quartz | Level 8
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;

View solution in original post

4 REPLIES 4
Tom
Super User Tom
Super User

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
Quartz | Level 8

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
Quartz | Level 8
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
Quartz | Level 8
Dear Tom, I appologize for the messy post. Thank you for trying to help!

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 166 views
  • 1 like
  • 2 in conversation