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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.