topic How to adjust for 100%? in SAS Procedures

How to adjust for 100%?

BETO — Wed, 28 Dec 2011 19:46:25 GMT

Hi,

Enclose is my script. What I want to create is a output that count total Compliance and divide by total of Opporunity grouping by week 1, week 2 week3 week4.

To look something like

Week1 Week2

No 5 50% 10 50%

Yes 5 50% 20 50%

Total 10 Total 30

One of the issue is when yes or no is blank for example in table Replen 2 or Replen 3 VSB1 an VSB2 the assumption was that was numbers for Yes and NO for week 1 but because the VSB was at 100% there were no NO it pushed VSB2 over one. It works when I have an entry for week 1 Yes an No week 2 Yes an No week 3 yes an No. Where it falters is when they are at 100% no or yes Week 1 has only Yes an Week 2 has Yes an No. Thanks for your assitance in this matter

proc sql; create table replen1 as select

VSB,

WEEK,

CompCheck,

COUNT(COMPCHECK) as cOMPLIANCE

from rEPLEN

WHERE vendorname eq ("Test1")

group by 1,WEEK,CompCheck;

quit;

proc sort data = Replen1;

by vsb ;

run;

proc transpose data=Replen1 out=SSP28 prefix=VSB;

by vsb ;

Var Week Compliance;

run;

data Yes(keep =VSB WEEK cOMPcHECK cOMPLIANCE rename =

(CompCheck =Yes))

No(keep =VSB WEEK cOMPcHECK cOMPLIANCE rename =

(CompCheck =No));

SET REPLEN1;

if COMPCHECK = 'Yes' then output YES;

if COMPCHECK = 'No' then output NO;

run;

proc transpose data=Yes out=Yes1 prefix=VSB;

by vsb ;

Var Week Compliance;

run;

proc transpose data=no out=no1 prefix=VSB;

by vsb ;

Var Week Compliance;

run;

Proc SQL;

Create Table Replen2 As

Select

VSB,

VSB1,

VSB2,

VSB3,

VSB4,

VSB5,

VSB6,

Sum(VSB1,VSB2) As SumWeek1

From SSP28;

quit;

Proc SQL;

Create Table Replen3 As

Select

VSB,

VSB1,

VSB2,

SumWeek1,

Divide(VSB2,SUMWEEK1)AS COMPLIANCEWEEK1

FORMAT percent10.

From Replen2;

quit;

VIEW OF REPLEN1

VSB	Week	CompCheck	cOMPLIANCE
SUB-T	1	Yes	12
SUB-T	2	Yes	8
SUB-T	3	Yes	8
SUB-T	1	No	2
SUB-T	1	Yes	45
SUB-T	2	No	2
SUB-T	2	Yes	31
SUB-T	3	No	1
SUB-T	3	Yes	29
SUB-R	1	No	3
SUB-R	1	Yes	30
SUB-R	2	No	2
SUB-R	2	Yes	23
SUB-R	3	No	1
SUB-R	3	Yes	22
SUB-N	1	Yes	41
SUB-N	2	Yes	28
SUB-N	3	Yes	28
SUB-M	1	No	1
SUB-M	1	Yes	54
SUB-M	2	Yes	40
SUB-M	3	Yes	39
SUB-L	1	Yes	11
SUB-L	2	Yes	8
SUB-L	3	Yes	8
SUB-H	1	Yes	29
SUB-H	2	Yes	20
SUB-H	3	Yes	20

View Replen2

VSB	VSB1	VSB2	VSB3	VSB4	VSB5	VSB6	SumWeek1
SUB-H	1	2	3	3
SUB-H	29	20	20	49
SUB-L	1	2	3	3
SUB-L	11	8	8	19
SUB-M	1	1	2	3	2
SUB-M	1	54	40	39	55
SUB-N	1	2	3	3
SUB-N	41	28	28	69
SUB-R	1	1	2	2	3	3	2
SUB-R	3	30	2	23	1	22	33
SUB-T	1	1	2	2	3	3	2
SUB-T	2	45	2	31	1	29	47
SUB-T	1	2	3	3
SUB-T	12	8	8	20

View Replen 3

VSB	VSB1	VSB2	SumWeek1	COMPLIANCEWEEK1
SUB-H	1	2	3	0.666666667
SUB-H	29	20	49	0.408163265
SUB-L	1	2	3	0.666666667
SUB-L	11	8	19	0.421052632
SUB-M	1	1	2	0.5
SUB-M	1	54	55	0.981818182
SUB-N	1	2	3	0.666666667
SUB-N	41	28	69	0.405797101
SUB-R	1	1	2	0.5
SUB-R	3	30	33	0.909090909
SUB-T	1	1	2	0.5
SUB-T	2	45	47	0.957446809
SUB-T	1	2	3	0.666666667
SUB-T	12	8	20	0.4

Added screen shots of Out put Replen was not added its 11 pages long. Thanks

Hi Patrick,

That is what I was looking for thanks so much. Final question How would I export output to Excel file ?

How to export out the output to Excel file?

How to adjust for 100%?

art297 — Wed, 28 Dec 2011 21:45:54 GMT

It will be easier to answer if you provide a sample rEPLEN dataset (preferably in the form of a datastep).

How to adjust for 100%?

BETO — Wed, 28 Dec 2011 23:05:19 GMT

Hi Art297,

I added screen shots

Re: How to adjust for 100%?

Patrick — Thu, 29 Dec 2011 02:38:19 GMT

Is it something like below you're after?

data have;
infile datalines dsd dlm=' ';
input Week VSB $ CompCheck $ VendorName $;
datalines;
1 SUB-H YES X
1 SUB-H YES X
1 SUB-H YES X
1 SUB-H YES X
2 SUB-H YES X
2 SUB-H YES X
2 SUB-H NO X
1 SUB-L YES X
1 SUB-L YES X
1 SUB-L NO X
1 SUB-L NO X
2 SUB-L YES X
2 SUB-L YES X
2 SUB-L YES X
2 SUB-L YES X
;
run;

options missing=' ';
proc tabulate data=have noseps;
where VendorName='X';
class Week VSB CompCheck;
keylabel all='Total';
table VSB*(CompCheck all), week*(n='N'*f=8.0 pctn<CompCheck all>='%')
/rts=30
;
run;

How to adjust for 100%?

BETO — Thu, 29 Dec 2011 04:45:14 GMT

HI Patrick,

That is exactly what I'm looking for.. Thanks I do have one more question how would I export the proc tab to an Excel file ?

How to adjust for 100%?

Patrick — Thu, 29 Dec 2011 08:55:04 GMT

Write the output to an ODS destination like below:

ODS HTML3 file='C:\temp\demo.xls' style=minimal;
options missing=' ';
proc tabulate data=have noseps;
where VendorName='X';
class Week VSB CompCheck;
keylabel all='Total';
table VSB*(CompCheck all), week*(n='N'*f=8.0 pctn<CompCheck all>='%')
/rts=30
;
run;
ODS HTML3 close;

More about ODS and Excel here:

http://support.sas.com/rnd/base/ods/excel/

How to adjust for 100%?

BETO — Mon, 02 Jan 2012 18:55:10 GMT

Happy New Year Patrick,

I have one more question I know how to format my % output when I'm dealing with Proc SQL table (E.G Percent10.). In the proc tabluate you assited me with where would I make referance to format Compcheck to format to look like 0.00% on the out put ? Thanks again

How to adjust for 100%?

Cynthia_sas — Mon, 02 Jan 2012 21:39:43 GMT

Hi:

Take a look at these Tech Support notes. PROC TABULATE does an automatic multiply by 100 when it calculates percents. The SAS-defined PERCENT format also does a multiply by 100. So you can't use the PERCENT automatic format to put a % into a TABULATE cell.

However, these Tech Support notes show how to use PROC FORMAT to define a PICTURE format that will put a % into a cell without doing an extra multiply by 100.

http://support.sas.com/kb/36/495.html

http://support.sas.com/kb/38/001.html

cynthia