DATA Step, Macro, Functions and more

Formatting counts in proc sql and datastep

Reply
Frequent Contributor
Posts: 141

Formatting counts in proc sql and datastep

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

 

 

Super User
Posts: 19,815

Re: Formatting counts in proc sql and datastep

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,

PROC Star
Posts: 283

Re: Formatting counts in proc sql and datastep

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

 

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;

PROC Star
Posts: 283

Re: Formatting counts in proc sql and datastep

Posted in reply to 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;

Ask a Question
Discussion stats
  • 3 replies
  • 92 views
  • 0 likes
  • 3 in conversation