BookmarkSubscribeRSS Feed
jcis7
Pyrite | Level 9
Hi, I have the following data:            
                 
vax1 vax2 vax3 county totalstudents year      
55 45 56 countyA 58 2015-16      
120 114 116 countyB 122 2015-16      
58 49 55 countyA 59 2016-17      
122 112 118 countyB 125 2016-17      
                 
                 
                 
I need the following output, ideally in excel:         
Of note: County is ordered ascending and listed only once and year is ordered descending.
                 
county year totalstudents vax1 Pct_vax1 vax2 Pct_vax2 vax3 Pct_vax3
countyA 2016-17 59 58 98.3% 49 83.1% 55 93.2%
  2015-16  58 55 94.8% 45 77.6% 56 96.6%
<extra blank line as spacer>            
countyB 2016-17 125 122 97.6% 112 89.6% 118 94.4%
  2015-16  122 120 98.4% 114 93.4% 116 95.1%
                 
                 
Thanks!                
3 REPLIES 3
Miracle
Barite | Level 11

Hi, how about this?

ods excel file='C:\Users\Desktop\want.xlsx' style=journal;
ods excel options(sheet_name='Want' autofit_height='yes' width_fudge='0.58');
proc report data=have nowindows headline headskip;
	column county year totalstudents vax1 Pct_vax1 vax2 Pct_vax2 vax3 Pct_vax3;
	define county / group order;
	define year / group order=internal descending;
	define totalstudents / analysis;
	define vax1 / analysis;;
	define Pct_vax1 / right computed format=percent7.1;
	compute Pct_vax1; Pct_vax1=vax1.sum/totalstudents.sum; endcompute;
	define vax2 / analysis;;
	define Pct_vax2 / right computed format=percent7.1;
	compute Pct_vax2; Pct_vax2=vax2.sum/totalstudents.sum; endcompute;
	define vax3 / analysis;;
	define Pct_vax3 / right computed format=percent7.1;
	compute Pct_vax3; Pct_vax3=vax3.sum/totalstudents.sum; endcompute;
	compute after county; line ' ';	endcomp;
run;  
ods excel close; run;
jcis7
Pyrite | Level 9

Thanks! This works!

One problem:  The MS Excel output is showing whole percentages instead of percentages including the tenths (i.e., 70% vs. 70.4%). The HTML output does show the percentages as expected. 

Appreciate your help.

atzamis
Obsidian | Level 7

Hi ,

this seems to work :

DATA TESTT;
FORMAT X PERCENT8.4;
X=0.052;
Y="A";
RUN;

 

proc format ;
picture pct
low-high='0000,99%' (mult=10000);
run;

 

ods excel file='filepath\filename.xlsx' options(sheet_interval='none' sheet_name="TEST" embedded_titles = "yes") style=Normal;

 

proc REPORT data=TESTT NOWD MISSING;
COLUMNS Y X ;
DEFINE Y / GROUP;
DEFINE X / ANALYSIS MEAN f=pct.;;

title ;

run;


ods excel close;

 

 

YX
A5,20%

 

 

 

you could try using excel xp

 

 


ods tagsets.ExcelXP file='path\filename.XML' options(sheet_interval='none' sheet_name="TEST" embedded_titles = "yes") style=Normal;
proc REPORT data=TESTT NOWD MISSING;
COLUMNS Y X;
DEFINE Y / GROUP;
DEFINE X / ANALYSIS MEAN style (column) ={tagattr='format:####.0000%'};

title ;

run;


ods tagsets.ExcelXP close;

 

YX
A5,2000%

 

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 662 views
  • 1 like
  • 3 in conversation