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
@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;
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;
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 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.