topic Re: Sumif equavalent in SAS in SAS Programming

Sumif equavalent in SAS

hvempati — Wed, 11 Jul 2018 20:51:35 GMT

I have two tables A and B.

Table A has Id, Item_Name, price_start_date, new_price_end_date

Table B has Covers, Date.

I want to create a code to add all the covers in Table A which lie in between Price_start_date and new_price_end_date (that is sum of covers between the dates in Table B).

I wrote a code to

Proc SQL;

create table work.covers_before_total as
Select a.*, Sum(Case When a.Price_start_Date <= a.NEW_Price_End_Date and a.Price_start_Date <= b.date and a.NEW_Price_End_Date >= b.date then b.Covers Else 0 END) as covers
from work.covers_before a,work.covers_measurement_model b

group by a.id

Run;

But the sum gives out 0 for some reason.

Is there also a better way of doing it?

Re: Sumif equavalent in SAS

art297 — Wed, 11 Jul 2018 21:19:58 GMT

Your code (once modified to meet what proc sql expects) appears to work correctly. I ran:

Proc SQL;
  create table work.covers_before_total as
    Select a.*, Sum(Case When a.Price_start_Date <= a.NEW_Price_End_Date and
                              a.Price_start_Date <= b.date and
                              a.NEW_Price_End_Date >= b.date then b.Covers Else 0 END) as covers
      from work.covers_before a,work.covers_measurement_model b
        group by a.id
  ;
quit;

Can't help further unless/until you provide some example data for the two datasets, and the result you expect.

Art, CEO, AnalystFinder.com

Re: Sumif equavalent in SAS

hvempati — Tue, 17 Jul 2018 16:14:27 GMT

Hi, @art297 Thank you for your reply. I had figured out that the input data I was giving was incorrect.