Equivalent of sumif in Excel

[i62328]

Hi,

I'll preface my question with the fact that I'm extremely new to SAS programming, so this is likely a really simple question.

I have a data set with 3 columns, 10,000 rows with columns = $ amount, indicator 1 with values of 1-30, and indicator 2 with values 1-30.

What I would like as an output is 3 columns, 30 rows with column 1 just listing 1 through 30, column 2 suming the $ amounts associated with Indicator 1 equilalent to my output column 1 , and column 3 suming the $ amounts associated with Indicator 2 equilalent to my output column1. Essentially a sumif excel idea.

Can someone give me a hand in figuring this out? Thanks so much in advance.

-TK

 

[ChrisW75]

I would say use proc summary or proc sql to sum the data. I would do this in 2 steps, first sum by Indicator1 and then a second sum by Indicator2, then join the 2 results files back together.

proc sql;
  create table indic1 as
  select indicator1  as indicator
        ,sum(amount)  as sum1
  from input_dset
  group by indicator1
  ;

  create table indic2 as
  select indicator2   as indicator
        ,sum(amount)  as sum2
  from input_dset
  group by indicator2
  ;
quit;

data final_results;
  merge indic1
        indic2;
  by indicator;
run;

Should do the trick nicely.
Proc summary does the job just as well as Proc SQL.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[i62328]

Thanks Chris!, I'm not familiar with the proc summary, but I ended up joining the two different intermediate steps like you suggested and that worked great. Thanks again,

-TK

 

[ChrisW75]

To use proc summary in place fo the SQL steps you'd code like this:-

proc summary data=input_dset nway;
  class indicator1;
  var amount;
  output out=indic1(drop=_type_ _freq_) sum(amount)=sum1;
run;

Proc means you will also hear about, it's EXACTLY the same procedure and syntax as Proc summary. The only difference is that MEANS produces printed output by default, and SUMMARY doesn't. All the doco for SUMMARY refers to the MEANS doco.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[i62328]

Thanks for the info!