topic Re: Need help to convert sas code to sql in New SAS User

Need help to convert sas code to sql

Venu1981 — Wed, 09 Nov 2022 20:07:53 GMT

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. **

Re: Need help to convert sas code to sql

Reeza — Wed, 09 Nov 2022 20:04:48 GMT

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

Re: Need help to convert sas code to sql

Venu1981 — Wed, 09 Nov 2022 20:10:06 GMT

yes that's acceptable.

Re: Need help to convert sas code to sql

Venu1981 — Wed, 09 Nov 2022 20:10:42 GMT

Hi Reeza, yes, that's acceptable.

Re: Need help to convert sas code to sql

Reeza — Wed, 09 Nov 2022 20:18:53 GMT

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.

Re: Need help to convert sas code to sql

Venu1981 — Wed, 09 Nov 2022 20:27:58 GMT

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

Re: Need help to convert sas code to sql

Tom — Wed, 09 Nov 2022 20:40:16 GMT

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

Re: Need help to convert sas code to sql

Reeza — Wed, 09 Nov 2022 20:40:53 GMT

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