About Skb19121985

Skb19121985 · ‎07-02-2017

Hi Sir Thanks for this ! This would be helpful. I will go through them and would try to figure out if I could do something more with it. Regards

Skb19121985 · ‎07-02-2017

Hi Reeza Thanks for the suggestions. It is in my mind. There are different ways to automate the process. I wanted to automate SAS and Excel together, to an extent that the only platform I work with would be SAS but I at the same time I take control of Excel using Excel VBA Macro. The Excel VBA code , I cited in my query was just an example. In short, I wanted to execute any Excel VBA Macro using SAS and get desired result in Excel. Ab initio, it is to be understood that no matter whatever be the Excel VBA Macro, it is tested to run error free in Excel. Regards

Skb19121985 · ‎07-02-2017

Hi Chris Thanks for the suggestion. Actually, I have just cited an example of Excel VBA Macro. In general, it can be any Excel VBA Macro. I just wanted to know the process through which I could execute any Excel VBA Macro using SAS. Regards,

Skb19121985 · ‎07-02-2017

Hi Sir Thanks for your reply. Actually , it is not EAD which is directly modelled, but CCF (Credit conversion factor), which in turn could be used to predict EAD. I have found a PDF at the link : https://www.sas.com/store/books/categories/usage-and-reference/developing-credit-risk-models-using-sas-enterprise-miner-and-sas-stat-theory-and-applications/prodBK_66220_en.html I have this PDF but can not attach it in this post due to large size. If you refer to Page 87 to 107, you will come to know that EAD model could be developed. You can read about them at : https://www.researchgate.net/publication/295161153_Exposure_at_default_models_with_and_without_the_credit_conversion_factor Actually, I followed this, but could not understand it thoroughly mainly because it is in SAS Enterprise Miner. Can we replicate the process in SAS EG ?

Skb19121985 · ‎07-02-2017

Hi Could anyone please help me with SAS code that would help execute Excel VBA Macro in SAS platform such that the whole process is automated ? I know how to open and run an Excel file in SAS , but I don't know how to implement a VBA code in my SAS. The SAS data that is exported in Excel is : Name Age Gender School DOB DOJ Qualification q 1 m qq 1 4 m w 2 m aa 2 5 m e 3 m aa 3 6 m r 4 m aa 1 4 m q 5 m aa 2 7 m w 6 m bb 3 56 m e 7 f bb 123 4 m r 8 f bb 1 5 b q 9 f bb 2 6 m w 0 f dd 3 4 b e 1 f dd 1 6 b q 1 f dd 2 4 m r 2 f dd 3 4 b q 2 m tt 1 5 m e 2 f tt 2 4 b r 3 m tt 3 6 m w 3 f tt 1 4 b e 4 m qq 2 4 m q 5 f qq 3 6 b e 5 m qq 1 4 m w 6 m qq 1 4 b r 7 m ww 2 65 m e 78 f ww 1 4 b w 8 f ww 2 4 m e 4 f ww 2 4 b r 5 f ww 3 6 m q 6 f ww 1 4 b w 7 f uu 1 6 m e 8 f uu 1 6 b I would like to implement the following Excel VBA code in SAS : Sub parse_data() Dim lr As Long Dim ws As Worksheet Dim vcol, i As Integer Dim icol As Long Dim myarr As Variant Dim title As String Dim titlerow As Integer vcol = 1 Set ws = Sheets("Sheet1") lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row title = "A1:C1" titlerow = ws.Range(title).Cells(1).Row icol = ws.Columns.Count ws.Cells(1, icol) = "Unique" For i = 2 To lr On Error Resume Next If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol) End If Next myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants)) ws.Columns(icol).Clear For i = 2 To UBound(myarr) ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & "" If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & "" Else Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count) End If ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1") Sheets(myarr(i) & "").Columns.AutoFit Next ws.AutoFilterMode = False ws.Activate End Sub

Skb19121985 · ‎07-02-2017

Hi Sir I understand that there are different ways of getting the required results without populating BQR1 to BQR12. The SAS code you presented is one such example. I just wanted wanted to understand the SAS process to get all the 3 queries addressed in the same SAS program. The SAS code that Patrick provided helped me also understand the SAS process through which I can populate some variables at time t by looking at the performance in next n months. Regards

Skb19121985 · ‎07-01-2017

Hi Patrick You are incredible ! Thanks for the help. This is what is required. Actually, to determine if an acct_id that has already been declared defaulted (basis BQR getting "9" in 12 months performance window) is written-off at a given observation date, BQR is observed for "W" in the next 12 months beginning the observation date with default_flag = 1in our practice instead of 60 as stated in my query. I am thinking of twigging the SAS statement such that the following is included in the program: %let write_of_months=12; The SAS code is working as required. Yes, I will be using this SAS code on 300 GB dataset. Will it be time-taking ? If Yes, kindly help me with complicated syntax that you have stated in your reply, if possible. Also, guide me with the study material that would help me become an expert SAS programmer just like you. As of now, I am trying to get hold of key words that you have used in the SAS program that you have shared on my post 🙂 Regards.

Skb19121985 · ‎07-01-2017

Hi Thanks for the reply. It is great to see that the default_date and write_off_date could be figured out for each acct_id through this code. Please note that my query is a bit different. 1) For each observation date corresponding to a given acct_id (consider this as first date of observation and then look forward into next 12 months data), I am expected to check in the next 12 months including the given observation month for BQR , if it gets a value 9 then declare that acct_id at the given date to have a default_flag = 1 else 0 against that record 2) As soon as an acct_id gets a default_flag = 1, I am expected to check further if in the next 60 months, BQR gets a value "W". If it gets a value "W", then I have to declare that acct_id at that given month to have write_off_flag = 1 else 0 3) I am also expected to create forward looking variables BQR_1 to BQR_12 and populate values from the variable BQR for the next 12 months I have tried to create a macro, however there is an issue. %macro zest(); Data answers; Set credit_data; By acct_id; Array _bqr(*) $ BQR1-BQR12; If first.acct_id then count=1; Else count + 1; %do i = 1 %to 12 ; if eof&i. = 0 then set credit_data (firstobs=&i. keep=BQR rename=(BQR=BQR&i.)) end=eof&i. ; if last.acct_id then BQR&i. = ""; %end ; write_off_flag=whichc('W', of _bqr(*)); default_flag=whichc('9', of _bqr(*)); count_max = max(count); Run ; %mend; %zest Whereever the difference between the date of observance and the last date for each acct_id is less than 12 months, the code is trying to overstep and evaluate the data from the next acct_id. I want this code to be suitably modified such that forward looking variables BQR1-BQR12 is correctly populated. To further clarify, if the difference between the date of observance (nth record) & last date of acct_id is less than 12, say 11 then in this case BQR12 should be populated as BQR12 = . against nth record. Hope my query is clear. If you run the above macro on the dataset SAS code given in the chain message, and check for the dataset named "answers" and observe BQR1-BQR12 aginst each nth record in the dataset, you will get to know what is exactly happening.

Skb19121985 · ‎06-27-2017

Hi Reeza I am almost near to what I need. Please have a look at the given SAS Code: %macro zest(); Data answers; Set credit_data; By acct_id; Array _bqr(*) $ BQR1-BQR12; If first.acct_id then count=1; Else count + 1; %do i = 1 %to 12 ; if eof&i. = 0 then set credit_data (firstobs=&i. keep=BQR rename=(BQR=BQR&i.)) end=eof&i. ; if last.acct_id then BQR&i. = ""; %end ; write_off_flag=whichc('W', of _bqr(*)); default_flag=whichc('9', of _bqr(*)); count_max = max(count); Run ; %mend; %zest However you will notice that in the dataset that gets created, row number 25 to 34 , all the values below the diagonal W are picked up from the acct_id 133 data, when they should all be blank Similarly, for row number 61 to 71, all the values below the diagonal W are W, when they should all be blank Can u please rectify the code ?

Skb19121985 · ‎06-27-2017

Hi Reeza Thanks for the reply, but this is not what I wanted. Let me clarify further. Let me focus on 1st query. I will take up remaining 2 queries in next thread one-by-one. Query1: I want to create default_flag for row1 - acct_id = 123 BQR=4 and Snapshot_dte = 01JAN1990 This default flag is created in such a way that in next 12 month , that is SAS would read all records related to this acct_id from 01JAN1990 to 01DEC1990 and check if BQR=9 ever in this 12 month time and flag the variable default_flag = 1 , if found else 0 if not found against row1 Similarly, when row2 - acct_id = 123 BQR=5 and Snapshot_dte = 01FEB1990 is read, the default flag is created in such a way that in next 12 month , that is SAS would read all records related to this acct_id from 01FEB1990 to 01JAN1991 and check if BQR=9 ever in this 12 month time and flag the variable default_flag = 1 , if found else 0 if not found against row2 This process is to be executed for each row forward looking into 12 months data and creating default_flag Hope the query is clear. Let me know if it is not clear That means, the SAS code should be such that internally it loop for next 12 months per row and create default flag

Skb19121985 · ‎06-27-2017

Could anyone help me with: 1) Conceptualization of EAD Modeling methodology using SAS Code 2) Data Preparation for the purpose of building EAD Model 3) SAS Code for EAD Model development

Skb19121985 · ‎06-27-2017

You can refer : 1) https://www.youtube.com/watch?v=Cbid8JcIiqs 2) https://www.youtube.com/watch?v=w0MqLCHhR_c 3)https://www.udemy.com/logistic-regression-credit-scoring-modelling-using-sas/

Skb19121985 · ‎06-27-2017

Please find the SAS dataset code here: data credit_data ; input acct_id $3. snapshot_date 6. BQR $8. ; cards; 123 9001 4 123 9002 5 123 9003 6 123 9004 7 123 9005 8 123 9006 4 123 9007 4 123 9008 5 123 9009 6 123 9010 7 123 9011 8 123 9012 3 123 9101 1 123 9102 2 123 9103 3 123 9104 4 123 9105 5 123 9106 4 123 9107 5 123 9108 7 123 9109 9 123 9110 9 123 9111 9 123 9112 9 123 9201 9 123 9202 9 123 9203 9 123 9204 9 123 9205 9 123 9206 9 123 9207 9 123 9208 9 123 9209 9 123 9210 9 123 9211 W 133 9101 1 133 9102 1 133 9103 1 133 9104 1 133 9105 2 133 9106 3 133 9107 4 133 9108 5 133 9109 4 133 9110 3 133 9111 5 133 9112 6 133 9201 7 133 9202 8 133 9203 7 133 9204 6 133 9205 5 133 9206 4 133 9207 5 133 9208 6 133 9209 7 133 9210 9 133 9211 9 133 9212 9 133 9301 9 133 9302 9 133 9303 9 133 9304 9 133 9305 8 133 9306 8 133 9307 8 133 9308 8 133 9309 8 133 9310 8 133 9311 9 133 9312 W ; Run ; Data credit_data (keep=snapshot_dte acct_id BQR BQR_1-BQR_12 default_flag write_off_flag) ; set credit_data ; snapshot_dte = mdy(mod(snapshot_date,100),1,int(snapshot_date/100)); format snapshot_dte date9. ; default_flag = . ; write_off_flag = . ; array BQRD(12) BQR_1-BQR_12 ; Run ; Hope this helps in proceeding further.

Skb19121985 · ‎06-27-2017

Hi Reeza Thanks for replying back. Actually, I have 3 questions as listed out. To reteriate , I wanted to flag an acct_id as default at a given observation date, if it is discovered to have defaulted with BQR=9 in the next 12 months. Secondly, once, the defaulted acct_id at a given observation date is identified, I wanted to flag it as written-off if the BQR="W" in the next 12 months Thirdly, I wanted to create variables BQR_1 to BQR_12 against each record (row) and populate the values of BQR as observed in next 12 months from the date of observation. Hope the query is clear by now. I am not able to think of the way to attempt at them so I have reached out directly on the forum. Regards, Sandeep

Skb19121985 · ‎06-27-2017

Kindly note that I have a query which has 3 parts. I am in look for three different SAS code to deal with them. 1) For each observation date corresponding to a given acct_id, I am expected to check in the next 12 months including the given observation month for BQR , if it gets a value 9 then declare that acct_id at the given date to have a default_flag = 1 else 0 2) As soon as an acct_id gets a default_flag = 1, I am expected to check further if in the next 60 months, BQR gets a value "W". If it gets a value "W", then I have to declare that acct_id at that given month to have write_off_flag = 1 else 0 3) I am expected to create variables BQR_1 to BQR_12 and populate values from the variable BQR for the next 12 months Please find the attached spreadsheet for sample query. Also note: 1) The start date of each acct_id need not be the same 2) There is no record available for the acct_id as soon as it gets BQR status as "W"

Online Status	Offline
Date Last Visited	‎07-24-2018 07:20 AM

Re: Execute Excel VBA Macro using SAS

Re: Execute Excel VBA Macro using SAS

Re: Execute Excel VBA Macro using SAS

Re: credit risk modelling - EAD Model

Execute Excel VBA Macro using SAS

Re: Credit Risk Model - Data Preparation using SAS Code

Re: Credit Risk Model - Data Preparation using SAS Code

Re: Credit Risk Model - Data Preparation using SAS Code

Re: Credit Risk Model - Data Preparation using SAS Code

Re: Credit Risk Model - Data Preparation using SAS Code

Re: Execute Excel VBA Macro using SAS

Re: Execute Excel VBA Macro using SAS

Re: Credit Risk Model - Data Preparation using SAS Code

Re: Execute Excel VBA Macro using SAS

Re: Credit Risk Model - Data Preparation using SAS Code

Re: Credit Risk Model - Data Preparation using SAS Code

Re: Execute Excel VBA Macro using SAS

Re: Execute Excel VBA Macro using SAS

Re: Execute Excel VBA Macro using SAS

Re: credit risk modelling - EAD Model

Execute Excel VBA Macro using SAS

Re: Credit Risk Model - Data Preparation using SAS Code

Re: Credit Risk Model - Data Preparation using SAS Code

Re: Credit Risk Model - Data Preparation using SAS Code

Re: Credit Risk Model - Data Preparation using SAS Code

Re: Credit Risk Model - Data Preparation using SAS Code

credit risk modelling - EAD Model

Re: credit risk modelling

Re: Credit Risk Model - Data Preparation using SAS Code

Re: Credit Risk Model - Data Preparation using SAS Code

Credit Risk Model - Data Preparation using SAS Code