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?
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;
It will be easier to answer if you provide a sample rEPLEN dataset (preferably in the form of a datastep).
Hi Art297,
I added screen shots
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;
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 ?
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:
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.