How to adjust for 100%?

[BETO]

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?

[art297]

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

[BETO]

Hi Art297,

I added screen shots

[Patrick · Accepted solution]

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;

[BETO]

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 ?

[Patrick]

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/

[BETO]

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

[Cynthia_sas]

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