Need insight

[NazaninSAS]

Hi,

I'm trying to produce this table. so far, I can produce the first two columns by Tabulate, but I would like to know how I can produce the rest of the columns?

Column A	Column B	Column C	Column D	Column E	Column F	Column G
	Total	Subset		Fixed percentage
EEOG	#	#	%	%
2	1417	76	=C14/B14	0.043	=B14*E14	=IF(F14-C14<0,0,F14-C14)
3	6900	417	=C15/B15	0.038	=B15*E15	=IF(F15-C15<0,0,F15-C15)
4	40	5	=C16/B16	0.046	=B16*E16	=IF(F16-C16<0,0,F16-C16)
5	79	6	=C17/B17	0.139	=B17*E17	=IF(F17-C17<0,0,F17-C17)
7	1871	148	=C18/B18	0.034	=B18*E18	=IF(F18-C18<0,0,F18-C18)
10	770	56	=C19/B19	0.07	=B19*E19	=IF(F19-C19<0,0,F19-C19)
Total	=SUM(B14:B19)	=SUM(C14:C19)	=C20/B20	0.04	=E20*B20	=IF(F20-C20<0,0,F20-C20)

I would appreciate your help and insight.

Thanks,

Nazanin

[RW9]

These are nothing more than simple formula, just do them in a datastep.

So your data (which you haven't provided) contains:
Cola colb colc

Cold is derived as;

cold=colc/colb;

Colf is;

colf=colb*cole;

And colg the same, just use an if.

[NazaninSAS]

my data is something like this: basically, my program has to look at this table, and generate the second column on total counts, and subset count is where the condition is DIS. and the rest of the columns of the output are calculations.

ID	EEOG	PWD
1	7
2	3	DIS
3	7
4	3
5	2
6	7
7	7
8	10
9	7
10	3	DIS
11	7

Thanks,

Nazanin

[ballardw]

---

@NazaninSAS wrote:

Hi,

I'm trying to produce this table. so far, I can produce the first two columns by Tabulate, but I would like to know how I can produce the rest of the columns?

Column A	Column B	Column C	Column D	Column E	Column F	Column G
	Total	Subset		Fixed percentage
EEOG	#	#	%	%
2	1417	76	=C14/B14	0.043	=B14*E14	=IF(F14-C14<0,0,F14-C14)
3	6900	417	=C15/B15	0.038	=B15*E15	=IF(F15-C15<0,0,F15-C15)
4	40	5	=C16/B16	0.046	=B16*E16	=IF(F16-C16<0,0,F16-C16)
5	79	6	=C17/B17	0.139	=B17*E17	=IF(F17-C17<0,0,F17-C17)
7	1871	148	=C18/B18	0.034	=B18*E18	=IF(F18-C18<0,0,F18-C18)
10	770	56	=C19/B19	0.07	=B19*E19	=IF(F19-C19<0,0,F19-C19)
Total	=SUM(B14:B19)	=SUM(C14:C19)	=C20/B20	0.04	=E20*B20	=IF(F20-C20<0,0,F20-C20)

I would appreciate your help and insight.

Thanks,

Nazanin

---

I believe this is at least the third if not fourth post on essentially the same topic.

You have not provided actual data that matches output in any of the posts. Screen shots are not data. Data copied and pasted from Excel/HTML/ or such are not data sets. Data sets have variable names, types, labels and formats. Hence we request data step code that will create a data set.

The "rules" for using your "condition" of DIS mentioned in a later post are not explicit and do not appear in the above output at all as far as I can see. Note that your above example does not include any value or reference to the condition(s) or variables that contain the values for those conditions.

Any rule not explicitly stated cannot be reliably implemented by guesses though some of us may get pretty close.

TABULATE is extremely limited as to what you calculate using two variables. Other than column/row statistics you are pretty much limited to PCTSUM and PCTN type calculations where you can indicate another variable n or sum as a denominator but sometimes your variables as is may not work. The Column F and Column G are right out as far as what proc tabulate is designed to do.

Your "fixed percentage" is not helping as your "example data" does not show that at all in the input.

Provide an actual subset of data, dummy values are okay as long as they behave the same as your actual data, with all variables involved and you may get a solution. Many times there are multiple steps involved. The procedures such as Tabulate and Report have many options but sometimes you have to pre-calculate bits and then use a report procedure for layout control.

[NazaninSAS]

input is this table:

ID	EEOG	PWD
1	7
2	3	DIS
3	7
4	3
5	2
6	7
7	7
8	10
9	7
10	3	DIS
11	7

ID is a unique number (employee number). for example, employee number 2 belongs to EEOG 03 (professionals) and that person had disability.

before, I used to run a pivot on Excel, to manually feed the out put table column by column.

pivot looked like this:

Row Labels	Sum of PRI	Count of PWD
2	5
3	16	1
5	12	1
7	37
10	8
Grand Total	78	2

Now, I want to use SAS to generate the output. I will define column E as constant in the SAS program. the rest of columns are calculations that have to be defined in SAS as well.

Thanks a lot for your help. this is a very small out put of a much more complicated output. If I can do this, hopefully, I can expand it.

best,

Nazanin

[Reeza]

I posted an answer in your previous threads. You should be able to take it from there, all that is missing is the E step, which don't really illustrate in this question either.

https://communities.sas.com/t5/SAS-Programming/How-to-add-quot-Total-quot-to-the-table/m-p/500424#M1...

[NazaninSAS]

Thanks a lot,

E is constant. I am not sure if I create another data set just to hold/get the value for E foe each EEOG, or if basically, I define in in SAS as constant on the top line.

value of E is constant

if EEOG=2 then E= 4.3%

if EEOG=3 then E=3.8%

if EEOG=5 then E=13.9%

if EEOG=7 then E= 3.4%

if EEOG=10 then E= 7%

for overall E= 4%.

I'm going to test the previous code.

best regards,

Nazanin

[ballardw · Accepted solution]

---

@NazaninSAS wrote:

Thanks a lot,

E is constant. I am not sure if I create another data set just to hold/get the value for E foe each EEOG, or if basically, I define in in SAS as constant on the top line.

value of E is constant

if EEOG=2 then E= 4.3%

if EEOG=3 then E=3.8%

if EEOG=5 then E=13.9%

if EEOG=7 then E= 3.4%

if EEOG=10 then E= 7%

for overall E= 4%.

I'm going to test the previous code.

best regards,

Nazanin

---

Likely means that you use code similar to that in a data step to add a variable. Note that if you want SAS to treat the value as a numeric then you the code above is not quite going to work.

You might want to look at a SELECT WHEN coding block as your "overall" might have issues with simple IF as done above.