Convert SQL Code to SAS

VALLY — Thu, 23 May 2019 09:11:08 GMT

please assist in converting the below code into SAS .

SELECT A.[KEY]

,A.[VOLUME]

,A.[DATE]

,A.[FLAG]

--,[PREV_MONTH] = DATEADD(MONTH,-1,A.[DATE])

,[DIFFERENCE] = CASE

WHEN CAST(A.[VOLUME] AS FLOAT) - CAST(B.[VOLUME] AS FLOAT) IS NULL THEN 0

ELSE CAST(A.[VOLUME] AS FLOAT) - CAST(B.[VOLUME] AS FLOAT)

END

,[SUM_] = CAST(A.[VOLUME] AS FLOAT) + CAST(B.[VOLUME] AS FLOAT)

FROM [ATM Index].[dbo].[CIB_FINALE_2] A

LEFT JOIN [ATM Index].[dbo].[CIB_FINALE_2] B

ON B.[KEY] = A.[KEY]

AND B.[FLAG] = A.[FLAG]

AND MONTH(B.[DATE]) = MONTH(DATEADD(MONTH,-1,A.[DATE]))

ORDER BY 1,3,4

Re: Convert SQL Code to SAS

Kurt_Bremser — Thu, 23 May 2019 09:35:01 GMT

Rough try:

proc sql;
select
  a.key,
  a.date,
  a.flag,
  intnx('month',a.date,-1) as prev_month,
  case
    when (a.volume - b.volume) is missing then 0
    else (a.volume - b.volume)
  end as difference,
  sum(a.volume,b.volume) as sum_
from cib_finale a left join cib_finale b
on
  a.key = b.key and
  a.flag = b.flag and
  month(intnx('month',a.date,-1)) = month(b.date)
order by 1,3,4
;
quit;

Mind that this will cause problems if data from more than one year is present in the source table. Could it be that the MONTH function in the database returns a combination of year and month?

topic Re: Convert SQL Code to SAS in New SAS User

Convert SQL Code to SAS

Re: Convert SQL Code to SAS