DATA Step, Macro, Functions and more

Sumif equavalent in SAS

Accepted Solution Solved
Reply
Highlighted
New Contributor
Posts: 2
Accepted Solution

Sumif equavalent in SAS

[ Edited ]

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?

 


Accepted Solutions
Solution
Tuesday
PROC Star
Posts: 8,167

Re: Sumif equavalent in SAS

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

 

View solution in original post


All Replies
Solution
Tuesday
PROC Star
Posts: 8,167

Re: Sumif equavalent in SAS

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

 

New Contributor
Posts: 2

Re: Sumif equavalent in SAS

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 88 views
  • 1 like
  • 2 in conversation