the first one was correct. I want the sum of the above rows! the second one gives me the total of all rows! Thanks, Nazanin ... View more

Thanks, I have missing values in SI-PRI. why do you think it will not give a correct answer?   Nazanin ... View more

That is just perfect. It works very well.   Thanks a lot, Nazanin ... View more

Hi Reeza,   I added the "case". can you please check and see where I'm wrong? Thanks Nazanin   data have; infile cards truncover;; input PRI EEOG $ PWD $; cards;   1 EEOG7 2 EEOG3 DIS 3 EEOG7 4 EEOG3 5 EEOG2 6 EEOG7 7 EEOG7 8 EEOG10 DIS 9 EEOG7 10 EEOG3 11 EEOG7 ; run; proc sql; create table want as select EEOG, count(*) as B, sum(PWD='DIS') as C, (calculated C/calculated B) as D format=percent12.1, Select EEOG   Case when 2 then 0.043 when 3 then 0.038 when 4 then 0.046 when 5 then 0.139 when 7 then 0.034 when 10 then 0.07 else 0.04 end as E from have calculated B* calculated E as F,   case when calculated F- calculated C<0 then 0 else calculated F- calculated C end as G from have group by EEOG   union select 'Total' as EEOG, count (*) as B, sum (PWD='DIS') as C, (calculated C / calculated B) as D format=percent12.1, 0.043 as E, calculated B* calculated E as F,   case when calculated F- calculated C<0 then 0 else calculated F- calculated C end as G from have; quit;   ... View more

That is just fantastic! Thanks a lot.   How can I somehow show EEOG10 before total?   I also have to assign these numbers to column E based on different EEOG for example, column E should be 0.043 for EEOG02 0.038 for EEOG03 and 0.046 for EEOG04.   Best regards,   Nazanin     ... View more

I want to generate this table: Column A Column B Column C Column D Column E Column F Column G   Total Subset   Fixed percentage for each EEOG     　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) ... View more

Thanks Reeza,   I was able to run it, but my problem is, the output only shows the total. Can you please send me your output?   Thanks,   Nazanin   ... View more

I hope you don't laugh at me! I'm totally new to SAS ... View more

data have; infile cards truncover;; input PRI EEOG $ PWD $   cards; 1 7 2 3 DIS 3 7 4 3 5 2 6 7 7 7 8 10 DIS 9 7 10 3 11 7   ; run;   　 proc sql; create table want as select EEOG, count(*) as B, sum(PWD='DIS') as C, (calculated C/calculated B) as D format=percent 12.1, 0.023 as E calculated B* calculated E as F,   case when calculated F-calculated C<0 then 0 else calculated F-calculated C end as G from have group by EEOG   union select 'Total' as EEOG, count (*) as B, sum (PWD='DIS') as C, (calculated C / calculated B) as D format=percent 12.1, 0.023 as E, calculated B* calculated E as F,   case when calculated F-calculated C<0 then 0 else calculated F-calculated C end as G from have; quit;     ------------------------------------------------ ERROR 22-322: Expecting a format name. ERROR 202-322: The option or parameter is not recognized and will be ignored. 359 0.023 as E 360 calculated B* calculated E as F, ---------- 78 76 ERROR 78-322: Expecting a ','. ERROR 76-322: Syntax error, statement will be ignored. 361 case 362 when calculated F-calculated C<0 then 0 363 else calculated F-calculated C 364 end as G 365 366 from have 367 group by EEOG 368 union 369 select 'Total' as EEOG, 370 count (*) as B, 371 sum (PWD='DIS') as C, 372 (calculated C / calculated B) as D format=percent 12.1, 373 0.023 as E, 374 calculated B* calculated E as F, 375 case 376 when calculated F-calculated C<0 then 0 377 else calculated F-calculated C 378 end as G 379 from have; 380 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.03 seconds cpu time 0.03 seconds ... View more

Thanks for your great help. I don't know why when I run the code, I receive errors.     ... View more

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 ... View more

E is fixed for each EEOG. for example, for EEOG 02, the number should be 0.043. ... View more

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 ... View more