Need help to convert sas code to sql

[Venu1981]

Here's what I see in SAS. Can someone help me with equivalent sql code of it please!

Indicator of how runout is handled cs_runout: 1 = extend from plan year end date,
2 = extend from termination, 3 = extend from date of claim, 4 = no runout 5 = Extend from grace period end;

IF cs_runout = 1 THEN DO;
  IF days = 15 THEN DO;
    IF ms_end=INTNX("MONTH",ms_end,0,"END") THEN runout=INTNX('MONTH',ms_end,runout_period,'END')+15;
%*If ms_end is last day of month then move to last day of future month then add 15;
    ELSE runout=INTNX('MONTH',ms_end,runout_period,'SAMEDAY')+15;
%*If ms_end is NOT last day of month then move to SAMEDAY of future month then add 15;
    put "Values in runout: " mb_subid= ms_end= runout_period= days= runout=;
  END;
  ELSE DO;
    IF ms_end=INTNX("MONTH",ms_end,0,"END") THEN runout=INTNX('MONTH',ms_end,runout_period,'END');
%*If ms_end is last day of month then move to last day of future month;
    ELSE runout=INTNX('MONTH',ms_end,runout_period,'SAMEDAY');
%*If ms_end is NOT last day of month then move to SAMEDAY of future month;
    put "Values in runout: " mb_subid= ms_end= runout_period= days= runout=;
  END;
*** ???? ***;
END;

Editted: ** I tried to convert what you posted into something that looks like SAS code.  **

[Reeza · Accepted solution]

SAS SQL or a different flavour of SQL?
It's basically CASE statements and you will lose the PUT functionality. Is that acceptable?

[Venu1981]

yes that's acceptable.

[Venu1981]

Hi Reeza, yes, that's acceptable.

[Reeza]

And SQL type? SAS SQL? That will affect if the INTNX function can be used.
Also, do you still need help, you've marked this solved.

[Venu1981]

Hi, any sql will do. It's not resolved, not sure who updated the status

[Tom]

---

@Venu1981 wrote:
Hi, any sql will do. It's not resolved, not sure who updated the status

---

You did.  You can unselect it.

Do you know SQL or not?

The syntax for CASE is different.  The result of the CASE clause is a value which you can then include as a variable in the list of variables in your select clause.

So your snippet is essentially this:

case when (cs_runout = 1) THEN 
  case when (days = 15) THEN 
    case when (ms_end=INTNX("MONTH",ms_end,0,"END")) THEN INTNX('MONTH',ms_end,runout_period,'END')+15
         else . 
    end
    else INTNX('MONTH',ms_end,runout_period,'SAMEDAY')+15
  else
    case when (ms_end=INTNX("MONTH",ms_end,0,"END")) THEN INTNX('MONTH',ms_end,runout_period,'END')
         else INTNX('MONTH',ms_end,runout_period,'SAMEDAY')
    end
  end
else .
end as runout

But to do a full translation you need to provide a full program.

For example instead of returning empty values for the ELSE steps in your data step code that did nothing to the RUNOUT variable perhaps you want to select the existing value of the RUNOUT variable (or some other variable) instead.

[Reeza]

case when (cs_runout=1 & days=15 & ms_end=INTNX("MONTH", ms_end, 0, "END")) then INTNX('MONTH', ms_end, runout_period, 'END')+15 
	when (cs_runout=1 & days=15 & ms_end ne INTNX("MONTH", ms_end, 0, "END")) then INTNX('MONTH', ms_end, runout_period, 'SAMEDAY')+15 
	when (cs_runout=1 & ms_end=INTNX("MONTH", ms_end, 0, "END")) then INTNX('MONTH', ms_end, runout_period, 'END') 
	when (cs_runout=1) then INTNX('MONTH', ms_end, runout_period, 'SAMEDAY') 
	else .
end as runout