How do list variable only once when you want to look at it over time

[jcis7]

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!

[Miracle]

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]

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]

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%