turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- How to adjust for 100%?

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-28-2011 02:46 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to BETO

12-28-2011 09:38 PM

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;

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to BETO

12-28-2011 04:45 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to art297

12-28-2011 06:05 PM

Hi Art297,

I added screen shots

Solution

12-28-2011
09:38 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to BETO

12-28-2011 09:38 PM

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;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Patrick

12-28-2011 11:45 PM

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 ?

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to BETO

12-29-2011 03:55 AM

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:

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Patrick

01-02-2012 01:55 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to BETO

01-02-2012 04:39 PM

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