Help using Base SAS procedures

How to adjust for 100%?

Accepted Solution Solved
Reply
Regular Contributor
Posts: 240
Accepted Solution

How to adjust for 100%?

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?


Accepted Solutions
Solution
‎12-28-2011 09:38 PM
Respected Advisor
Posts: 4,173

Re: How to adjust for 100%?

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;

View solution in original post


All Replies
PROC Star
Posts: 7,474

How to adjust for 100%?

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

Regular Contributor
Posts: 240

How to adjust for 100%?

Hi Art297,

I added screen shots

Solution
‎12-28-2011 09:38 PM
Respected Advisor
Posts: 4,173

Re: How to adjust for 100%?

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;

Regular Contributor
Posts: 240

How to adjust for 100%?

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 ?

Respected Advisor
Posts: 4,173

How to adjust for 100%?

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/

Regular Contributor
Posts: 240

How to adjust for 100%?

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

SAS Super FREQ
Posts: 8,866

How to adjust for 100%?

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 207 views
  • 1 like
  • 4 in conversation