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! |
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;
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.
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;
Y | X |
A | 5,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;
Y | X |
A | 5,2000% |
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.