Then you can add a CASE statement in to assign the values or store it in a table and merge it in.
@NazaninSAS wrote:
E is fixed for each EEOG.
for example, for EEOG 02, the number should be 0.043.
Thanks for your great help.
I don't know why when I run the code, I receive errors.
Post the code and errors. It works on the sample data you provided.
Is it not working in terms of incorrect results or just not working.
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
I hope you don't laugh at me! I'm totally new to SAS
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
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) |
CHECK YOUR LOG and fix errors in the order they appear.
You'll see you missed a semicolon on the INPUT statement as well so your data never read in correctly. You excluded that part of the log and errors so I didn't notice it either.
You also have some typos in your code. Compare it line to line to mine (look for missing comma's at the end and spaces) then you'll get the correct results.
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
Add another CASE statement based on the EEOG values that will assign the correct value.
@NazaninSAS wrote:
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
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;
Case when EEOG=2 then 0.04
When EEOG=3 then 0.005
...
...
else 0
end as MulitplyFactor
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.