topic Re: What is the logic for this if statment that I need to use it proc sql? in SAS Programming

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

GN0001 — Tue, 14 Jun 2022 23:50:21 GMT

Hello team,

I need to write a couple of if statements, how can I do it?

If LineOfBusiness eq 'Medicare' Then Measure in ( AAB, CCD)...

Should I map one one one?

The reason for this that some of the measures are not mapping to that LineofBusiness and they are not available for some specific Line of businesses.

How can I do this in proc sql in a case statement or in an if statement?

Regards,

Bitter & Sweet

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

Tom — Wed, 15 Jun 2022 00:09:25 GMT

You need to explain what it is you are trying to do as the statement you posted is invalid. What goes after the THEN in and IF/THEN statement is another statement. Instead you just have another comparison.

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

SASKiwi — Wed, 15 Jun 2022 00:11:21 GMT

Using a CASE statement is the way to go with SQL. The IF statement isn't available. It isn't clear from what you've posted what rules you want to apply. Start by writing it in English, then it is easy to create a CASE statement.

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

GN0001 — Wed, 15 Jun 2022 16:20:40 GMT

Hello team,

To give you some context:

I have measures such as measure a, measure b, measure c, measure d

I have Line of business such Medicare, Medicaid, Covered California, Exchange HMO

Now not all Line of businesses have these measures.

For example:

Medicare has measure a, measure b, measure c

Medicaid has measure a, measure b, measure d

Covered California has measure d, measure a

and so on.

I need to put these in a proc sql.

How can I do that?

I appreciate your stepping in!

Bitter & Sweet,

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

GN0001 — Wed, 15 Jun 2022 16:21:53 GMT

Please read the top part.

I posted some explanation.

Respectfully,

Bitter & Sweet

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

Tom — Wed, 15 Jun 2022 16:44:50 GMT

I still do not see any actual question here.

What are the VARIABLES that you have? What type are they? Numeric or Character?

It sounds a little like you are trying to say you have two variables.

One might be INSURANCE which can have values like 'Medicare' or 'Medicaid'. So that would need to be a character variable. Or if it was a numeric variable with values like 1,2,3,etc you could use a fomat to display the numbers as those descriptive string.

Another might be MEASURE which could have values like 'A' or 'B'.

Now what does the full dataset consist of? Are you tracking individual insurance companies and whether they provide insurance policies under Medicare or Medicaid? Are you tracking individual people and what insurance they have? Are you tracking the billing for individual procedures that a patient received and what insurance was used to pay for that procedure?

Once you describe the data you have you can then try to explain what you are trying to do that you need help with.

Are you having trouble entering the data into the dataset?

Are you trying to test if the data in the dataset follows some rules? What rules? What do want to do if the rules are violated?

Are you trying to convert data from one format to another?

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

GN0001 — Wed, 15 Jun 2022 17:27:42 GMT

Hello Tom,

As simple as I said.

None of the cases you said. If I want to explain what our department does, that makes it more complicated and I need to explain about the strategy of United States Health plans.

Some of the insurances don't have all measures.

As simple as this.

For example:

if LineOfBusiness eq 'Medicare' then measure eq 'measure A'

if LineOfBusiness eq 'Medicare' then measure eq 'measure B'

if LineOfBusiness eq 'Medicare' then measure in ('measure A', 'measure B',..)

I need to add this to a proc sql which is already there. I need to add this part to my code.

No need to change the code because that brings more complication. I need to add this part to my code only.

Thanks,

Bitter & Sweet

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

Tom — Wed, 15 Jun 2022 18:05:10 GMT

That is not code and it is not a description.

In particular what do you mean by this:

if LineOfBusiness eq 'Medicare' then measure eq 'measure A'

If the second EQ was an equal sign then it is a valid statement that means when LINEOFBUSINESS is 'Medicare' then set MEASURE to 'measure A'.

Are you just trying to express a complex boolean expression? If so then use AND and not IF/THEN.

(LineOfBusiness eq 'Medicare') and ( measure eq 'measure A')

That expression will be true when LINEOFBUSINESS is 'Medicare' and MEASURE is 'measure A'.

You could then use such an expression in a IF/THEN statement. Or in SQL in the WHEN part of a CASE expression.

In either case what do want to DO when the expression is true? What do you want to do when the expression is false?

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

GN0001 — Wed, 15 Jun 2022 18:14:56 GMT

Hello,

What I am trying to do is when line of business is Medicare, then measures such sas measureA, MeasureB and MeasureC are applied to Medicare line of business.

Should I say?

If LineofBusiness eq upcase(Medicare) Then measure in ('measureA', 'measureB', 'Measurec');

If LineofBusiness eq upcase(Commercial) Then measure in ('measureA', 'measureB', 'MeasureD');

Something like above.

I have a proc sql that selects LineofBusiness and measure, I need to add the part on the top to this proc sql.

Thanks,

p.s. Each record has one line of business and one measure in the measure field. How do I need to write this code?

Bitter & Sweet.

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

Tom — Wed, 15 Jun 2022 19:06:31 GMT

@GN0001 wrote:

Hello,

What I am trying to do is when line of business is Medicare, then measures such sas measureA, MeasureB and MeasureC are applied to Medicare line of business.

Should I say?

If LineofBusiness eq upcase(Medicare) Then measure in ('measureA', 'measureB', 'Measurec');

If LineofBusiness eq upcase(Commercial) Then measure in ('measureA', 'measureB', 'MeasureD');

Something like above.

I have a proc sql that selects LineofBusiness and measure, I need to add the part on the top to this proc sql.

Thanks,

p.s. Each record has one line of business and one measure in the measure field. How do I need to write this code?

Bitter & Sweet.

You are still not describing anything concrete. Once you can describe you can probably code it yourself.

Are you saying you have a QUERY written in SQL syntax that is selecting records from an existing dataset that has a variable named LineofBusiness and a variable named measure and you want to subset to just those observations where the combination of LINEOFBUSINESS and MEASURE are in a limited set of values?

So perhaps something like:

select * 
from have
where (LineofBusiness eq upcase('Medicare')   and measure in ('measureA', 'measureB', 'Measurec') )
   or (LineofBusiness eq upcase('Commercial') and measure in ('measureA', 'measureB', 'MeasureD') )
;

So it would exclude observations where measure was 'MeasureD' but LINEOFBUSINESS was not 'COMMERCIAL'.

Basically exclude the combinations that had an invalid MEASURE for that line of business.

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

GN0001 — Wed, 15 Jun 2022 19:15:04 GMT

Hello Tom,

I don't need "and" or "or"

I said if line of business is medicare then measures are measure A, measure B, measure c

This is what I want in code.

Respectfully,

bitter & sweet

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

GN0001 — Wed, 15 Jun 2022 19:17:18 GMT

Hello Tom,

Invalid measure is taken care of in a different part of the code.

Whatever we have is valid measures and valid line of business.

Respectfully,

bitter & sweet

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

GN0001 — Wed, 15 Jun 2022 19:21:16 GMT

Hello,

How can I write this in a code either in if statement or case statement?

If lineofbusiness is medicare then measures are measureA, measureB, measurec.

Thanks,

Bitter & Sweet

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

Tom — Wed, 15 Jun 2022 19:26:20 GMT

@GN0001 wrote:

Hello,

How can I write this in a code either in if statement or case statement?

If lineofbusiness is medicare then measures are measureA, measureB, measurec.

Thanks,

Bitter & Sweet

If lineofbusiness is medicare is a TEST of the value of a variable. So what do want to HAPPEN then? You cannot set the value of MEASURE to three different values. It can only have one at a time.

In a data step you could GENERATE multiple observations using code like:

data want;
  set have;
  if lineofbusiness = 'Medicare' then do measure = 'measureA', 'measureB', 'measurec';
    output;
  end;
run;

So every observation that met the IF condition is converted into three observations where each one has a different value of measure. Is that what you are trying to DO .

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

Cynthia_sas — Wed, 15 Jun 2022 21:13:46 GMT

Hi:

To offer a different perspective, this seems like a table lookup possibility with a user-defined format more than an IF, DO or CASE application. With a long list of possibilities, if only one variable is needed (called Measure in my program), then a user-defined format can accomplish that. I've put an example below with some fake data, so you can see the approach.

Cynthia

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

GN0001 — Wed, 15 Jun 2022 21:56:56 GMT

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.

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

Tom — Wed, 15 Jun 2022 22:12:57 GMT

@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;

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

GN0001 — Wed, 15 Jun 2022 22:37:35 GMT

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

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

Tom — Wed, 15 Jun 2022 23:09:30 GMT

@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

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

GN0001 — Wed, 15 Jun 2022 23:34:20 GMT

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