Help using Base SAS procedures

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

Reply
Regular Contributor
Posts: 192

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

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!                
Regular Contributor
Posts: 217

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

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;
Regular Contributor
Posts: 192

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

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.

Occasional Contributor
Posts: 10

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

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%

 

Ask a Question
Discussion stats
  • 3 replies
  • 135 views
  • 1 like
  • 3 in conversation