SAS code to SQL using Alteryx

Question

Hello everyone,

I'm working on little complex problem, converting SAS code to SQL. I have attached the sample data. Data is order by Account number and Month end date.

Here is the SAS code:

by accountnumber;

retain status;

if first.accountnumber = 1 then status = 0;

if lag(accountstatusdescription) = 'Active' AND accountstatusdescription in ('Chargeoff', 'Closed', 'Non-Accrual') then status = 1;

if status > 0 then status + 1;

if lag(accountstatusdescription) = 'Active' AND accountstatusdescription in ('Chargeoff', 'Closed', 'Non-Accrual') then status = status - 1;

Problem : How to use first. variable and retain in SQL/Alteryx?.

I'm able to achieve the later part in  after  if first.accountnumber = 1 then status = 0 Alteryx using multi-row formula.  Like this :

IF [Account status Desc]='Active'
THEN 0
ELSEIF [Account status Desc]!='Active' AND [Account status Desc]!=[Row-1:Account status Desc]
THEN 1
ELSE [Row-1:Status Alteryx]+1
ENDIF

But what I need is if the first. account number then status =0 and that status will retain, please see row 24 in the spreadsheet.

Please let me know if I didn't explain it properly or need more information.

Thank you for your help in advanced!

SAS_TO_SQL_Test_Data.xlsx

danilang · Answer

Hi @tk12

If you add in a clause to check if [AccountNumber] = [FirstAccountNumber] like this

IF [AccountNumber] = [FirstAccountNumber] then
0
elseIF [AccountStatusDescription]='Active'
THEN 0
ELSEIF [AccountStatusDescription]!='Active' AND [AccountStatusDescription]!=[Row-1:AccountStatusDescription]
THEN 1
ELSE [Row-1:Status Alteryx]+1
ENDIF

you can duplicate the results of you original status column

The 0 value that I used just matches your sample data.  You can replace this with additional logic as required

Dan

SAStoAlteryx.yxzp

tk12 · Answer

Hi @jagdeeshn,

Thank you for quick response.

The data sets that I'm working on is for credit card. How our systems works is, if you lost or assigned new credit card system will assign new account number. But we need to find out which one is first account number out of group of account number. For example person number 150, has 5 accounts 1,2,3,4,5 from 1980- 2020, in this case 1 will be the first account number, hence we have column called "First account number".

If you look at the data sets, first 23 rows, status changes to "1" as soon as it hits account status description as close and start incrementing. Now for row 24 to 31 status will remain 0 even though it's account status description is close, in this case we assume since it's first in the group it's active account and status should be 0, it should retain and not increment.

Make sense? Please le me know if you need more information.

Thank you!

tk12 · Answer

Hi @Jagdeeshn,

Thank you for quick response.

This data is for credit card, what happened is when customer lost or assigned new credit card, our system assigned new account number, so we need to first find out , out of let say 5 account number which is the original/ first account number, and that's where we have "First Account number" column, and it will remain the same for the panel data.

What is Panel data: For the particular person number  let say 150, if he has 5 different account number 1,2,3,4,5 over the time from 1980 -2020. In this case 1 will be the "First Account number" .

If you look at the example I provided first account number never change, only the account number associated with that user will change. In the same example, for first 23 rows where Account number is 4, and first account number is 1, but as soon as we have "CLOSE" account status description, then STATUS will start incrementing from 1 till the end or hits active again.

But for row 24, account number 1 is 1st in the group even though status is close we assume it's active and we assign 0 value for status, and that status should remain and don't increment.

Make sense?

Please let me know if you need more information or need more example!

Thank you!