topic Re: Formatting counts in proc sql and datastep in SAS Programming

Formatting counts in proc sql and datastep

Q1983 — Mon, 13 Nov 2017 17:41:00 GMT

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

Re: Formatting counts in proc sql and datastep

Reeza — Mon, 13 Nov 2017 17:54:48 GMT

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,

Re: Formatting counts in proc sql and datastep

novinosrin — Mon, 13 Nov 2017 19:43:17 GMT

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

Re: Formatting counts in proc sql and datastep

novinosrin — Mon, 13 Nov 2017 19:58:58 GMT

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;