BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BETO
Fluorite | Level 6

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

7 REPLIES 7
art297
Opal | Level 21

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

BETO
Fluorite | Level 6

Hi Art297,

I added screen shots

Patrick
Opal | Level 21

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
Fluorite | Level 6

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
Opal | Level 21

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
Fluorite | Level 6

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
SAS Super FREQ

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

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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