What is the logic for this if statment that I need to use it proc sql?

[GN0001]

Hello Cyntia,

Thanks for the response. 

This is my table in real life:

measure                     coveredCalifornia    Medicare    Medicaid HMO

measureA                          X                           X              N/A

measureB                         N/A                         X              X

measureC                        X                              X             N/A

Now, I want the result doesn't show the measure when it is N/A for a healthplan.

That is the best I can explain it.

Respectfully,

Bitter & Sweet.

[Tom]

---

@GN0001 wrote:

Hello Cyntia,

Thanks for the response. 

This is my table in real life:

measure                     coveredCalifornia    Medicare    Medicaid HMO

measureA                          X                           X              N/A

measureB                         N/A                         X              X

measureC                        X                              X             N/A

Now, I want the result doesn't show the measure when it is N/A for a healthplan.

That is the best I can explain it.

Respectfully,

Bitter & Sweet.

---

Now you are getting closer.  

But that table looks to me like it might be the description of the rules about which plans cover which "measure" (whatever you mean by that).

Now the question is what does the actual data you are trying to operate on look like?

That would probably look something more like:

data have ;
   input id :$10. date :date. measure :$10. cost plan $20.;
cards;
1 01JAN2022 MeasureA 100 Medicare
1 02FEB2022 MeasureB 200.50 Medicare
2 03MAR2022 MeasureC 10.75 coveredCalifornia
;

So take your coverage table and convert into a tall format instead:

data coverage;
  input measure :$10. @;
  length plan $20 coverstring $3 covered 8;
  do plan= 'coveredCalifornia','Medicare', 'Medicaid HMO';
    input coverstring @ ;
    covered = (coverstring='X');
    output;
  end;
cards;
measureA                          X                           X              N/A
measureB                         N/A                         X              X
measureC                        X                              X             N/A
;

So you have a useful dataset like this:

 Obs    measure     plan                 coverstring    covered
  1     measureA    coveredCalifornia        X             1   
  2     measureA    Medicare                 X             1   
  3     measureA    Medicaid HMO             N/A           0   
  4     measureB    coveredCalifornia        N/A           0   
  5     measureB    Medicare                 X             1   
  6     measureB    Medicaid HMO             X             1   
  7     measureC    coveredCalifornia        X             1   
  8     measureC    Medicare                 X             1   
  9     measureC    Medicaid HMO             N/A           0  

Now you can combine your coverage table with the actual data at create the COVERED flag to indicate if the event is covered by that plan.

proc sql;
create table want as
select a.*, b.covered
   from have a
  left join coverage b
  on a.plan = b.plan and a.measure = b.measure
;
quit;

[GN0001]

Hello Cynthia,

The join is already happened in a proc sql and I'd like the rule for which I showed a table to be applied.

I need to add this rule to a proc sql by if statement or case when.

I appreciate your response.

Bitter & Sweet

[Tom]

---

@GN0001 wrote:

Hello Cynthia,

The join is already happened in a proc sql and I'd like the rule for which I showed a table to be applied.

I need to add this rule to a proc sql by if statement or case when.

I appreciate your response.

Bitter & Sweet

---

Either add another JOIN to the SQL to combine the coverage table.

Or convert the table into the code I posted before with the OR that you said you did not want.  For example using a CASE clause to create another variable in the list of variables being selected by the SQL query.

case when (plan='Medicare' and measure in ('measureA','measuerB')) then 1
     when (plan='Medicaid' and measure in ('measureA','measureC')) then 1
         ...
     else 0
end as coverage  

[GN0001]

Hello Tom,

Yes, This is what I am looking for. In prior posts, I said no "and" or "or", I was wrong. Can we put the code in a way that if the condition is met, then don't bring that row in to the result. I mean delete that row.

Thanks,

Bitter & Sweet

[Tom]

That is what the WHERE statement I posted earlier was for. To select the observations to include in the result set of the SQL query.