topic Re: How do list variable only once when you want to look at it over time in SAS Procedures

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

jcis7 — Mon, 27 Feb 2017 16:43:50 GMT

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!

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

Miracle — Mon, 27 Feb 2017 19:20:49 GMT

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;

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

jcis7 — Mon, 27 Feb 2017 23:29:12 GMT

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.

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

atzamis — Fri, 10 Mar 2017 09:50:08 GMT

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%