Formatting counts in proc sql and datastep

[Q1983]

proc sql;

create table test1 as

select state, ln_no, mod_in_yr, balance

from test

;quit;

OUTPUT
State    ln_no    mod_in_yr          balance

  AR       100            Y                    100

 CA        102            Y                    200

CA         104            Y                    200

  DE        103            N                   100
DE          106            N                   100
DE          107           N                    100               

proc sql;

create table my_summ as

select State, mod_in_yr,

        case when mod_in_yr = 'N' then sum(balance) = 0 else sum(balance) end as mysum,

        case when mod_in_yr = 'N' then count(ln_no) = 0 else count(ln_no)  end as mycount

where mod_in_yr = 'Y'

;quit;

DESIRED OUTPUT
State  mysum     mycount
AR       100             1
CA       400             2
DE        0                0        I want to show 0 for DE since it did not meet the mod_in_yr requirements however I do not want the code to skip it, just apply a 0 and show it      

Note ln_no is a char variable

[Reeza]

1. You have no GROUP BY statement showing you want results for each separate group.
2. SUM/COUNT as designated here will work on a single row, if you want it to apply to all values, then use it BEFORE the CASE.

sum ( case when mod_in_Yr ='N' then 0 else balance end) as new_variable,

etc....

3. I suspect you aren't actually referring to FORMATS as you mentioned in your title but please clarify if you are.

Here's how you specific a format in SQL

sum(...) as new_variable format=8.2,

[novinosrin]

@Q1983  I would recommend using boolean math expression for your sample +Distinct keyword. Chilling and easy 🙂

data have;

input State  $  ln_no    mod_in_yr    $      balance;

datalines;

AR       100            Y                    100

 CA        102            Y                    200

CA         104            Y                    200

  DE        103            N                   100

DE          106            N                   100

DE          107           N                    100

;

proc sql;

create table want as

select distinct state, sum(balance*(mod_in_yr="Y")) as mysum,  count(balance)*(mod_in_yr="Y") as mycount

from have

group by state;

quit;

[novinosrin]

Datastep for your sample, still the same boolean approach:

data have;

input State  $  ln_no    mod_in_yr    $      balance;

datalines;

AR       100            Y                    100

 CA        102            Y                    200

CA         104            Y                    200

  DE        103            N                   100

DE          106            N                   100

DE          107           N                    100

;

data want;

set have;

by state;

if first.state then do;

mysum=0;

mycount=0;

end;

mysum+balance;

mycount+1;

if last.state then do;

mysum=mysum*(mod_in_yr="Y");

mycount=mycount*(mod_in_yr="Y");

output;

end;

keep state mysum mycount;

run;