Scott_Mitchell Tracker

DI Studio SQL Merge Transformation User Guide

Scott_Mitchell — Thu, 28 Sep 2023 07:44:38 GMT

Hi All,

I am not a big user of DI Studio, but find myself in a scenario where I need to use the SQL Merge Transformation, to update and insert records in an SCD2 Oracle Table. I have worked my way through the user guide and have really not found much information on this transformation. I have read this article http://databobjr.blogspot.com/2011/04/load-slowly-changing-dimension-type-2.html, which has given me a lot of insight into how the code should be structured, but having taken a number of stabs at replicating the logic in DI, I have failed miserably.

I have searched Google (of course), but I am yet to find anything that addresses this topic at length. Does anyone have good resources around how to use this transformation at all?

Thanks in advance for any assistance offered.

Edit: This is what I have been able to come up with so far. It appears the problem I am having is getting the subqueries to nest correctly. This is the code I have generated so far:

MERGE INTO dim_product p
USING ( SELECT DECODE(s.scd_row_type_id,1,-6789,m.product_sid) as product_sid,
                   PRODUCT_ID,
                   PRODUCT_CATEGORY_ID,
                   PRODUCT_NAME,
                   PRODUCT_DESCRIPTION,
                   PRICE,
                   PRICE_EFFECTIVE_DATE,
                   LAST_MODIFIED_DATE,
                   CREATED_DATE,
                   m.scd_row_type_id
              FROM    (SELECT dp.product_sid,
                              sp.PRODUCT_ID,
                              sp.PRODUCT_CATEGORY_ID,
                              sp.PRODUCT_NAME,
                              sp.PRODUCT_DESCRIPTION,
                              sp.PRICE,
                              sp.PRICE_EFFECTIVE_DATE,
                              sp.LAST_MODIFIED_DATE,
                              sp.CREATED_DATE,
                              CASE
                                 WHEN dp.product_id IS NULL
                                 THEN
                                    1
                                 WHEN (dp.product_category_id !=
                                          sp.product_category_id
                                       OR dp.product_name != sp.product_name
                                       OR DP.PRODUCT_DESCRIPTION !=
                                             sp.product_description
                                       OR dp.price != sp.price
                                       OR dp.price_effective_date !=
                                             sp.price_effective_date)
                                 THEN
                                    2
                                 ELSE
                                    0
                              END
                                 AS scd_row_type_id
                         FROM    stg_product sp
                              LEFT JOIN
                                 Dim_product dp
                              ON (sp.product_id = dp.product_id and  dp.is_current_row = 'Y')
                     ) m
                   JOIN  scd_row_type s
                   ON (s.scd_row_type_id <= m.scd_row_type_id)
                   ) mp
        ON  (p.product_sid = mp.product_sid)
when matched then
   update set P.EFFECTIVE_END_DATE = mp.LAST_MODIFIED_DATE, is_current_row = 'N', updated_date = sysdate
  commit;

Re: Excel AMO - Deleting Stored Process Object and Retaining Values using VBA

Scott_Mitchell — Sun, 28 Jul 2019 23:06:47 GMT

@fifthand57th - Thank you so much. Once again proving you "Know All"..

I was actually very close to this result from my investigation over the weekend, but your solution is far more elegant.

One change I did have to make was to the string within the third argument of the InStr function. If I left it as is I was getting a new SAS Add-In for Microsoft Office panel on the right hand side of the screen as per the screenshot below. This continues beyond the point where the entire screen is covered in panels.

Unsure why this is the case, but given my solution didn't do this I created an amalgam of the two using _AMO_SingleObject_ as opposed to _AMO_ and then deleted the STP using the Delete method.

Sub DeleteSTPObject()
    
    ' Iterates through a list of objects used within the ThisWorkBook
    ' If prefixed with _AMO_  then delete the stored process location.
    
    Dim names As names
    Set names = ThisWorkbook.names
    
    For i = names.Count To 1 Step -1
    
        Dim n As Name
        Set n = names.Item(i)
        
        If InStr(1, n.Name, "_AMO_SingleObject_", vbTextCompare) = 1 Then
            Debug.Print names.Item(i)
            n.Delete
        End If
    
    Next i

End Sub

I hope you are having a wonderful vacation and thank you again for your insight.

Regards,

Scott Mitchell

Excel AMO - Deleting Stored Process Object and Retaining Values using VBA

Scott_Mitchell — Fri, 26 Jul 2019 00:12:13 GMT

Hi All and specifically @fifthand57th (who seems to know all),

I have two stored processes:

The first takes input entered into various cells within the Excel spreadsheet, queries a Dataset and returns the values to 64 non-contiguous cells.

The second takes the values from the 64 non-contiguous cells (some values in these cells will be changed by the end-user, hence why there needs to be a second stored process) and calls a series of code elements to transform the data so that it can be loaded into another table.

Sounds pretty simple, however I am unable to pass the values from the 64 cells as prompts to the second stored process because the data is stored in the first stored process object and therefore can't be used as prompt values. To counter this issue I decided to record a macro by selecting the "Close Select Items" from the home tab in the automatically surfaced SAS Panel (as per the attached screenshot).

This does precisely what I want it to do and disconnects from stored process object whilst retaining the values (something that the delete method fails to do). The VBA generated by this process is:

Sub Macro2()
'
' Macro2 Macro
'

'
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.0").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.1").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.2").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.3").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.4").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.5").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.6").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.7").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.8").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.9").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.10").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.11").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.12").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.13").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.14").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.15").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.16").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.17").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.18").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.19").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.20").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.21").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.22").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.23").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.24").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.25").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.26").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.27").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.28").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.29").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.30").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.31").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.32").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.33").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_AFFINITY_BRAND").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_AFFINITY_ENTITY").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_AFFINITY_GROUP").Delete
    ActiveWorkbook.Names("_AMO_ContentLocation_194379916_OutputParameter_DD_BD_AGE" _
        ).Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_BD_GENDER").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_BD_LICENCE_HELD_MTHS"). _
        Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_BD_LICENCE_TYPE").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_BD_OTHER_VEHICLE").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_NO_DRIVERS_ALLOWED").Delete
    ActiveWorkbook.Names("_AMO_ContentLocation_194379916_OutputParameter_DD_RD_AGE" _
        ).Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_RD_CLMS_CNT_NW_1YR").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_RD_CLMS_CNT_NW_5YR").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_RD_GENDER").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_RD_IS_YOUNGEST_DRIVER"). _
        Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_RD_LICENCE_HELD_MTHS"). _
        Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_RD_LICENCE_TYPE").Delete
    ActiveWorkbook.Names("_AMO_ContentLocation_194379916_OutputParameter_DD_RD_NCD" _
        ).Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_RD_OCCUPATION_STATUS"). _
        Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_RD_OTHER_VEHICLE").Delete
    ActiveWorkbook.Names("_AMO_ContentLocation_194379916_OutputParameter_DD_YD_AGE" _
        ).Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_YD_GENDER").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_YD_LICENCE_HELD_MTHS"). _
        Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_YD_LICENCE_TYPE").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_YD_OTHER_VEHICLE").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_YDNL_AGE").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_YDNL_LICENCE_HELD_MTHS"). _
        Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_YDNL_LICENCE_TYPE").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_YDNL_OTHER_VEHICLE").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_PO_BREAKDOWN").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_PO_CHOICE_OF_REPAIRER").Delete
    ActiveWorkbook.Names("_AMO_ContentLocation_194379916_OutputParameter_PO_DRIVER" _
        ).Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_PO_HIRE_CAR").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_PO_NCD_PROTECTION").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_PO_RATE_1_FOR_LIFE").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_PO_WINDSCREEN").Delete
    ActiveWorkbook.Names("_AMO_ContentLocation_194379916_OutputParameter_PRODUCT"). _
        Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_RAD_METHOD_OF_PARKING").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_RAD_POSTCODE").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_VD_ACCESS_VALUE").Delete
    ActiveWorkbook.Names("_AMO_ContentLocation_194379916_OutputParameter_VD_AGE"). _
        Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_VD_AGREED_MARKET_VALUE").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_VD_ALARM_FITTED").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_VD_BODY_ENGINE_MOD").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_VD_BODY_STYLE").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_VD_BULL_BAR_FITTED").Delete
    ActiveWorkbook.Names("_AMO_ContentLocation_194379916_OutputParameter_VD_COLOUR" _
        ).Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_VD_DRIVE_TYPE").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_VD_ENGINE_TYPE").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_VD_FACTORY_OPTIONS").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_VD_FINANCE_TYPE").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_VD_INSURED_VALUE").Delete
    ActiveWorkbook.Names("_AMO_ContentLocation_194379916_OutputParameter_VD_KMS"). _
        Delete
    ActiveWorkbook.Names("_AMO_ContentLocation_194379916_OutputParameter_VD_MAKE"). _
        Delete
    ActiveWorkbook.Names("_AMO_ContentLocation_194379916_OutputParameter_VD_MODEL") _
        .Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_VD_PREVIOUS_INS").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_VD_USE_CODE").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_AFFINITY_BRAND").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_AFFINITY_ENTITY").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_AFFINITY_GROUP").Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_DD_BD_AGE"). _
        Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_DD_BD_GENDER" _
        ).Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_BD_LICENCE_HELD_MTHS").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_BD_LICENCE_TYPE").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_BD_OTHER_VEHICLE").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_NO_DRIVERS_ALLOWED").Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_DD_RD_AGE"). _
        Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_RD_CLMS_CNT_NW_1YR").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_RD_CLMS_CNT_NW_5YR").Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_DD_RD_GENDER" _
        ).Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_RD_IS_YOUNGEST_DRIVER").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_RD_LICENCE_HELD_MTHS").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_RD_LICENCE_TYPE").Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_DD_RD_NCD"). _
        Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_RD_OCCUPATION_STATUS").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_RD_OTHER_VEHICLE").Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_DD_YD_AGE"). _
        Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_DD_YD_GENDER" _
        ).Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_YD_LICENCE_HELD_MTHS").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_YD_LICENCE_TYPE").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_YD_OTHER_VEHICLE").Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_DD_YDNL_AGE") _
        .Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_YDNL_LICENCE_HELD_MTHS").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_YDNL_LICENCE_TYPE").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_YDNL_OTHER_VEHICLE").Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_PO_BREAKDOWN" _
        ).Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_PO_CHOICE_OF_REPAIRER").Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_PO_DRIVER"). _
        Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_PO_HIRE_CAR") _
        .Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_PO_NCD_PROTECTION").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_PO_RATE_1_FOR_LIFE").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_PO_WINDSCREEN").Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_PRODUCT"). _
        Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_RAD_METHOD_OF_PARKING").Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_RAD_POSTCODE" _
        ).Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_VD_ACCESS_VALUE").Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_VD_AGE"). _
        Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_VD_AGREED_MARKET_VALUE").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_VD_ALARM_FITTED").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_VD_BODY_ENGINE_MOD").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_VD_BODY_STYLE").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_VD_BULL_BAR_FITTED").Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_VD_COLOUR"). _
        Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_VD_DRIVE_TYPE").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_VD_ENGINE_TYPE").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_VD_FACTORY_OPTIONS").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_VD_FINANCE_TYPE").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_VD_INSURED_VALUE").Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_VD_KMS"). _
        Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_VD_MAKE"). _
        Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_VD_MODEL"). _
        Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_VD_PREVIOUS_INS").Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_VD_USE_CODE") _
        .Delete
End Sub

The problem is that every time a new stored process object is created a new object number is allocated (which makes complete sense). The question is, is this stored somewhere that I can obtain it, and build it into my VBA so regardless of the identifier for the object I will be able to disconnect the Stored Process object?

Any help or insight would be greatly appreciated.

Regards,

Scott

Re: SAS AMO - Passing Output Parameters To Excel

Scott_Mitchell — Wed, 10 Jul 2019 03:25:31 GMT

@fifthand57th - It turned out to be a very simple (in hindsight at least) VBA issue, stemming from the original code attached to the article referenced previously.

    For i = 1 To list.Count
 
        Dim stp As SASStoredProcess
        Set stp = list.Item(i)
        ' Change the path below to the folder where your stored process was saved
       If (stp.Path = "/User Folders/scm/My Folder/Motor Premium Emulator Proof of Concept Pull Data V0_1") Then
           stp.Refresh
           Exit Sub
      End If
            
   Next i

My understanding of this chunk of code initially was, if the STP already existed then it would perform a refresh of the input parameters and output parameters that have been defined and re-execute the STP using the newly defined parameters then it would exit the sub routine. In actuality (from what I am now observing), the stored process is definitely passing the input parameter name to the STP, but it had no idea what output parameters were to be returned (I assume this is because the first time I executed the STP I didn't hadn't defined output parameters yet within the VBA). I deleted the STP from the Excel spreadsheet using the AMO interface and executed it again and bingo everything populates as desired. I then removed the offending code and replaced it with code which deletes the STP from the Excel Sheet before inserting the STP again. It now works like a charm.

    
Sub DeleteAllStoredProcesses()

    Dim sas As SASExcelAddIn
    Set sas = Application.COMAddIns.Item("SAS.ExcelAddIn").Object

    Dim list As SASStoredProcesses
    Set list = sas.GetStoredProcesses(Sheet1)
    
    If list.Count() > 0 Then
    
        Dim stp As SASStoredProcess
        Set stp = list.Item(1)
        stp.Delete
    
    End If

End Sub


Sub DeleteandPullFromDS()

    DeleteAllStoredProcesses
    PULL_FROM_SAS_DATASET

End Sub

For completeness sake, is there anything I am saying above that doesn't stand true?

Thank you again for all your help.

Regards.

Scott

Re: SAS AMO - Passing Output Parameters To Excel

Scott_Mitchell — Wed, 10 Jul 2019 02:09:57 GMT

Thank you for the paper @fifthand57th.

I have employed the method of checking the results discussed in the paper and I am getting the anticipated results:

So the issue is the VBA (which doesn't surprise me as I haven't used VBA in about 10 years).

I will continue working on this issue, but if you have an suggestions I would greatly appreciate any insights.

Regards,

Scott

SAS AMO - Passing Output Parameters To Excel

Scott_Mitchell — Tue, 09 Jul 2019 03:04:57 GMT

Good Afternoon Everyone,

I am hoping someone may be able to help me as I am pulling my hair out on this one.

I am required to pull back a single record to Excel based on a series of Input Parameters. For the sake of brevity I have limited the example to a single Input Parameter and a Single Output Parameter within the VBA code. The reason I am using Output Parameters as opposed to reading in a single record back into Excel is because the results need to be output to various cells within the spreadsheet and not in tabular form.

Example of the Desired Output

VBA

Sub PULL_FROM_SAS_DATASET()

    Dim sas As SASExcelAddIn
    Set sas = Application.COMAddIns.Item("SAS.ExcelAddIn").Object
    
    'Delete and entries in the existing log
 
    sas.ClearLog
    
    'Setting Options
 
    sas.Options.ResetAll
    sas.Options.AutoInsertResultsIntoDocument = True
    sas.Options.PromptForParametersOnRefreshMultiple = False
    sas.Options.ShowStatusWindow = False
    sas.Options.ShowStatusWindow = False
    sas.Options.ShowSASLog = True
    sas.Options.ShowStatusWindow = False
    sas.Options.Excel.ShowDataInfoInStatusBar = False
    sas.Options.Excel.ShowPlaceholderForEmptyResults = False

    'Create a list of all stored processes within the workbook.
    
    Dim list As SASStoredProcesses
    Set list = sas.GetStoredProcesses(ThisWorkbook)
    
    'If the stored process within the is equal to the the value stored in stp.path then refresh the results
    
    For i = 1 To list.Count
 
        Dim stp As SASStoredProcess
        Set stp = list.Item(i)
        ' Change the path below to the folder where your stored process was saved
       If (stp.Path = "/User Folders/scm/My Folder/Motor Premium Emulator Proof of Concept Pull Data V0_1") Then
           stp.Refresh
           Exit Sub
      End If
            
   Next i
 
   ' Input streams for the stored process
    Dim prompts As SASPrompts
    Set prompts = sas.CreateSASPromptsObject
    
    ' Modify the range where the user is typing in the input data.
    ' Make sure this range includes the label that will be used as the variable name
    Dim IN_NAME As Range
    Set IN_NAME = Sheet1.Range("IN_NAME")
    
    prompts.Add "IN_NAME", IN_NAME
    
    ' Output parameter locations that have been added to the stored process
    Dim outputParams As SASRanges
    Set outputParams = New SASRanges
        
    Dim OUT_NAME As Range
    Set OUT_NAME = Sheet1.Range("OUT_NAME")
     
    outputParams.Add "OUT_NAME", Sheet1.Range("B5")

    
    'Capture The Prompts To Be Used As Parameters Within The Stored Process
    ' Modify the parameters for the method below to the location of your stored process
    ' Here is the syntax of the method
    ' object.InsertStoredProcess(path,outputLocation,[prompts],[outputParameters],[inputStreams])
    
    Set stp = sas.InsertStoredProcess("/User Folders/scm/My Folder/Motor Premium Emulator Proof of Concept Pull Data V0_1", Sheet1.Range("A20"), prompts, outputParams)


End Sub

I was inspired by http://support.sas.com/kb/42/983.html in my approach to the proposed solution, however instead of an input stream, I am using Input Parameters (because the information used for Input Parameters will be in various places within the sheet and will be non-contiguous).

Stored Process

%IF %LENGTH(&IN_NAME.) > 0 %THEN %DO;

DATA PULL_PRICING_LOGS;
	SET SASHELP.CLASS;
	WHERE UPCASE(NAME) = UPCASE("&IN_NAME.");
	CALL SYMPUTX("OUT_NAME",NAME,"GLOBAL");
	CALL SYMPUTX("OUT_AGE",AGE,"GLOBAL");
	CALL SYMPUTX("OUT_SEX",SEX,"GLOBAL");
	CALL SYMPUTX("OUT_WEIGHT",WEIGHT,"GLOBAL");
	CALL SYMPUTX("OUT_HEIGHT",HEIGHT,"GLOBAL");
RUN;

%END;

%PUT &=OUT_NAME &=OUT_AGE &=OUT_SEX &=OUT_WEIGHT &=OUT_HEIGHT;

The VBA executes without errors and the relevant Input Parameters are passed to the Stored Process successfully. The following is output to the log and indicates that the macro variables are being created successfully.

Log Resulting From Stored Process Call

30         %IF %LENGTH(&IN_NAME.) > 0 %THEN %DO;
31         
32         DATA PULL_PRICING_LOGS;
33         	SET SASHELP.CLASS;
34         	WHERE UPCASE(NAME) = UPCASE("&IN_NAME.");
35         	CALL SYMPUTX("OUT_NAME",NAME,"GLOBAL");
36         	CALL SYMPUTX("OUT_AGE",AGE,"GLOBAL");
37         	CALL SYMPUTX("OUT_SEX",SEX,"GLOBAL");
38         	CALL SYMPUTX("OUT_WEIGHT",WEIGHT,"GLOBAL");
39         	CALL SYMPUTX("OUT_HEIGHT",HEIGHT,"GLOBAL");
40         RUN;

NOTE: There were 1 observations read from the data set SASHELP.CLASS.
      WHERE UPCASE(NAME)='ALFRED';
NOTE: The data set WORK.PULL_PRICING_LOGS has 1 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      

41         
42         %END;
43         
44         %PUT &=OUT_NAME &=OUT_AGE &=OUT_SEX &=OUT_WEIGHT &=OUT_HEIGHT;
OUT_NAME=Alfred OUT_AGE=14 OUT_SEX=M OUT_WEIGHT=112.5 OUT_HEIGHT=69

Stored Process Created Parameters

Unfortunately, the results from the macro variables are not being passed back from SAS to Excel in order to populate the defined cell. If anyone has an idea as to where I am going wrong I would appreciate any insight you could offer.

@fifthand57th - Tagging you in this post, as you were so wonderfully helpful with my last query.

Thanking you all in advance.

Scott

Re: SAS AMO - Silent Execution of Stored Process

Scott_Mitchell — Tue, 09 Jul 2019 02:25:06 GMT

@fifthand57th Thank you so much for this. Greatly appreciated.

SAS AMO - Silent Execution of Stored Process

Scott_Mitchell — Thu, 27 Jun 2019 00:35:03 GMT

Hi All,

I have created a stored process which I want to execute silently i.e. without the "Executing SAS Analysis" Popup or the Information window appearing on the right hand side of the screen as per below. Is there an option that can stop these things occurring either from the interface or via VBA?

Any help would be greatly appreciated.

Thank you in advance.

Re: PROC FCMP - Output Character Value Truncation

Scott_Mitchell — Tue, 04 Jun 2019 07:04:00 GMT

Worked perfectly. Thank you.

Re: PROC FCMP - Output Character Value Truncation

Scott_Mitchell — Mon, 03 Jun 2019 09:49:01 GMT

Thank you @Ksharp . I will give this a shot tomorrow.

PROC FCMP - Output Character Value Truncation

Scott_Mitchell — Fri, 31 May 2019 05:42:52 GMT

Hi All,

Running SAS 9.4M5.

I am looking to clean up a list of potential variable names and as a result I decided to create a custom function. The problem is, even though I have explicitly set the length (excessively large at 500) of the resulting variable, I still max out at 20 characters and some of the data is being truncated. I have tested the PRXCHANGE syntax in a stand alone datastep and it works as desired, however when I copy the exact syntax into FCMP the truncation occurs.

The following is a subset list of potential variable names:

DATA HAVE;
INFILE DATALINES DLM=",";
INPUT BUFFER : $200.;
DATALINES;
Policy Number
Vehicle Sequence Number
Vehicle Suffix
Vehicle Seqno - Financials
Status
;
RUN;

The Proc FCMP step:

PROC FCMP OUTLIB=WORK.FUNCTIONS.BUSREQS;

LENGTH NEW_NAME $ 500;

FUNCTION CLEANCOLUMNNAMES(OLD_NAME $) $ 500;
NEW_NAME = PRXCHANGE('s/[^a-z A-Z 0-9_]/ /',-1,TRIM(LEFT(OLD_NAME)));

RETURN (NEW_NAME);

ENDSUB;

RUN;

OPTIONS CMPLIB = (WORK.FUNCTIONS);

Datastep using the newly created variable:

DATA WANT;
SET HAVE;
LENGTH TEST $ 500;
TEST = CLEANCOLUMNNAMES(BUFFER);
RUN;

Results showing the truncated results:

Any assistance would be greatly appreciated.

Regards,

Scott

Re: Querying Metadata using Proc Metadata

Scott_Mitchell — Mon, 06 May 2019 22:30:17 GMT

22         /* Assign filerefs and libref. */
23         filename query "&datapath.\results2.xml" lrecl=256;
24         filename rawdata "&datapath.\results3.xml" lrecl=256;
25         filename map temp;
26         libname myxml xml xmlfileref=rawdata xmlmap=map;
NOTE: Libref MYXML was successfully assigned as follows: 
      Engine:        XML 
      Physical Name: 
27         
28         /* Create temporary query file.                           */
29         /* 2309 flag plus template gets table name, column name,  */
30         /* engine, libref, and object IDs. The template specifies */
31         /* attributes of the nested objects.                      */
32         
33         data _null_;
34            file query;
35            input;
36            put _infile_;
37            datalines;

NOTE: The file QUERY is:
      Filename=\\SASVM\SAS-DATA\LEV1\DATA\USER SANDPITS\SMLL\CLH SAS FORUM\DATA\results2.xml,
      RECFM=V,LRECL=256,File Size (bytes)=0,
      Last Modified=07 May 2019 08:29:12,
      Create Time=06 May 2019 13:50:53

NOTE: 14 records were written to the file QUERY.
      The minimum record length was 80.
      The maximum record length was 80.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
52         ;;

53         run;
2                                                          The SAS System                                 08:26 Tuesday, May 7, 2019

54         
55         proc metadata
56              in=query
57              out=rawdata;
58         run;

NOTE: PROCEDURE METADATA used (Total process time):
      real time           2.68 seconds
      cpu time            0.09 seconds
      

59         
60         
61         data _null_;
62            file map;
63            input;
64            put _infile_;
65            datalines;

NOTE: The file MAP is:
      Filename=F:\SAS\Lev1\SASWORK\_TD10044_SASVM_\#LN00282,
      RECFM=V,LRECL=32767,File Size (bytes)=0,
      Last Modified=07 May 2019 08:29:14,
      Create Time=07 May 2019 08:29:14

NOTE: 20 records were written to the file MAP.
      The minimum record length was 80.
      The maximum record length was 80.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
86         ;

87         Run;
88

@ballardw Log added as requested.

Querying Metadata using Proc Metadata

Scott_Mitchell — Mon, 06 May 2019 04:57:37 GMT

Hi Everyone.

I am attempting to query metadata using Proc Metadata and am having some problems. I got the base query from https://support.sas.com/documentation/cdl/en/lrmeta/60739/HTML/default/viewer.htm#a003177416.htm (which works perfectly and seems to make sense when I compare the map and the query to the Metadata Browser) and am attempting to modify it to suit my needs. Initially I am attempting to get the name of all jobs registered in Metadata, then identify those which are deployed and lastly which flows the jobs are part of.

When I execute the following query the resulting table contains no data. Can anyone help get me back on track please? I have read all the documentation and it doesn't seem to help. I have also read a number of whitepapers, however converting the logic they are using to fit my use case is problematic.

Any assistance is greatly appreciated.

Regards,

Scott

/* Assign filerefs and libref. */
filename query "&datapath.\results2.xml" lrecl=256;
filename rawdata "&datapath.\results3.xml" lrecl=256; 
filename map temp; 
libname myxml xml xmlfileref=rawdata xmlmap=map;

/* Create temporary query file.                           */
/* 2309 flag plus template gets table name, column name,  */
/* engine, libref, and object IDs. The template specifies */
/* attributes of the nested objects.                      */

data _null_;
   file query;
   input;
   put _infile_;
   datalines;
<GetMetadataObjects>
   <Reposid>$METAREPOSITORY</Reposid>
   <Type>Job</Type>
   <Objects/>
   <Ns>SAS</Ns>
   <Flags>2309</Flags>
   <Options>
    <Templates>
      <Job/>
        <Column Name=""/>
    </Templates>
   </Options>

</GetMetadataObjects>
;;
run;

proc metadata
     in=query
     out=rawdata;
run;


data _null_;
   file map;
   input;
   put _infile_;
   datalines;

<?xml version="1.0" ?>
  <SXLEMAP version="1.2">

   <TABLE name="JobDetails"> 
   <TABLE-PATH syntax="xpath">
     /GetMetadataObjects/Objects/Job/
   </TABLE-PATH>

   <COLUMN name="Name" retain="yes">
     <PATH>
     	/GetMetadataObjects/Objects/Job/
     </PATH>
     <TYPE>character</TYPE>
     <DATATYPE>STRING</DATATYPE>
     <LENGTH>60</LENGTH>
   </COLUMN>
   </TABLE>
   
</SXLEMAP>
;
Run;

Re: Learning Visual Investigator and Social Network Analysis

Scott_Mitchell — Wed, 21 Nov 2018 07:35:50 GMT

Hi David,

Thank you very much for your insight. Unfortunately, I don't work for an organisation with the appropriate licencing (or any SAS licencing to be honest) so have been unable to access the online documentation. It was more of a self-driven skill development activity.

Once again I greatly appreciate you taking the time to explain your experiences (it sounds like we are working from a very similar base) with the product and I am really looking forward to getting my hands on it in the future.

Regards,

Scott

Learning Visual Investigator and Social Network Analysis

Scott_Mitchell — Sun, 18 Nov 2018 07:45:01 GMT

Good Evening Everyone.

I am interested in learning more about Visual Investigator and Social Network Analysis. Obviously, SAS Training is the place to go, however, I would like to have a reasonable understanding of the topics before attending. Can anyone recommend a good place to begin my journey?

Thank you for your help.

Regards,

Scott

Re: Replace Numeric variables values

Scott_Mitchell — Mon, 09 Jul 2018 07:26:04 GMT

Using the Input Statement ?? format modifier allows the value to be set to missing, suppresses the warning message in the log and sets the _ERROR_ automatic variable to 0. The same applies to the Input function.

data want;
input numval ??;
cards;
1
2
3
-
4
;
run;

Re: PROC REPORT - Summarising Data Using Different Classification Variables

Scott_Mitchell — Tue, 19 Jun 2018 22:51:19 GMT

Hi @Cynthia_sas,

As always you are a star. Your insights confirm my suspicions, but was hoping there was another way of approaching it. There are 3 reports per segment, so I was hoping to reduce the amount of code, alas that is unavoidable.

For completeness sake: Testing was simply used whilst developing the report, which was always going to be removed anyway. Unfortunately I can't remove the UNIFORM option as it is a requirement tables across pages be sized the same.

Thank you again for your reply.

Regards,

Scott

PROC REPORT - Summarising Data Using Different Classification Variables

Scott_Mitchell — Tue, 19 Jun 2018 03:44:36 GMT

Good Afternoon All,

SAS Version : 9.4M4

OS: Windows Server 2012 R2

I haven't used PROC REPORT in quite some time so need a little refresher.

I have obtained results for the first report table I am looking to output (please see the below code - SYNTAX 1), but am having a few problems with adding subsequent tables.

SYNTAX 1


/*CREATE EXAMPLE DATA*/
DATA SHOES ;
	SET SASHELP.SHOES (IN = A KEEP = PRODUCT SUBSIDIARY SALES)
		SASHELP.SHOES (IN = B KEEP = PRODUCT SUBSIDIARY SALES);
	
	IF B AND _N_ = 396 THEN ITEM = 0;

	IF B THEN DO;
	     SALES = SALES *.15;
	     SUBSIDIARY = "London";
	END;

	ITEM + 1;

	BOOTS 		= 1;
	MEN_CASUAL	= 1;
	MEN_DRESS   = 1;
RUN;


OPTIONS  LEFTMARGIN = .5IN RIGHTMARGIN = .5IN ORIENTATION = LANDSCAPE CENTER MISSING="" NODATE NONUMBER;

ODS LISTING CLOSE;
ODS HTML CLOSE;
ODS PDF FILE= "&WORKPATH.\SHOES.PDF" UNIFORM;

PROC REPORT DATA = SHOES OUT = TESTING SPLIT = '*' NOWD ;
	COLUMNS ITEM PRODUCT SUBSIDIARY SALES SALES = BOOTSSALES SALES = MENCASUALSALES SALES = MENDRESSSALES BOOTS MEN_CASUAL MEN_DRESS
			BOOTS MEN_CASUAL MEN_DRESS;
	DEFINE ITEM  		  / GROUP "Item";
	DEFINE PRODUCT 		  / GROUP;
	DEFINE SUBSIDIARY 	  / GROUP;
	DEFINE BOOTS		  / GROUP NOPRINT;
	DEFINE MEN_CASUAL	  / GROUP NOPRINT;
	DEFINE MEN_DRESS   	  / GROUP NOPRINT;
	DEFINE BOOTSSALES 	  / SUM NOPRINT;
	DEFINE MENCASUALSALES / SUM NOPRINT;
	DEFINE MENDRESSSALES  / SUM NOPRINT;

   	COMPUTE ITEM;
		ITEM2 = ITEM;
		IF MOD(ITEM2,2) THEN DO;
         	CALL DEFINE(_ROW_, "STYLE", "STYLE=[BACKGROUND=#f2f2f2]");
      	END;
   	ENDCOMP;


	COMPUTE SALES;
		IF	UPCASE(TRIM(LEFT(PRODUCT))) = "BOOT" THEN DO;
			H_CNTBOOTS+1;
			H_SALESBOOTSCASH+SALES.SUM;
		END;

		ELSE IF UPCASE(TRIM(LEFT(PRODUCT))) = "MEN'S CASUAL" 	 THEN DO;
			H_CNTMENCASUAL+1;
			H_SALESMENCASUAL+SALES.SUM;
		END;

		ELSE IF UPCASE(TRIM(LEFT(PRODUCT))) = "MEN'S DRESS" THEN DO;
			H_CNTMENDRESS+1;
			H_SALESMENDRESS+SALES.SUM;
		END;
	ENDCOMP; 

	COMPUTE AFTER;
		LINE @1 " ";
		LINE @1 "NUMBER OF BOOT SALES :" @34 H_CNTBOOTS ;
		LINE @1 "VALUE OF BOOT SALES :" 	@34 H_SALESBOOTSCASH DOLLAR18.2;
		LINE @1 "NUMBER OF MEN'S CASUAL SALES :" @34 H_CNTMENCASUAL; 
		LINE @1 "VALUE OF MEN'S CASUAL SALES :" @34 H_SALESMENCASUAL DOLLAR18.2;;
		LINE @1 "NUMBER OF MEN'S DRESS SALES :" @34 H_CNTMENDRESS;
		LINE @1 "VALUE OF MEN'S DRESS SALES :" @ 34 H_SALESMENDRESS DOLLAR18.2;;
		LINE @1 " ";
	ENDCOMP;

RUN;

ODS PDF CLOSE;
ODS LISTING;
ODS HTML;

I want a table similar to the one generated by the below syntax (SYNTAX 2), but was unsure if there was a way to produce the same output using a single PROC REPORT. My concern is merely processing time to read the dataset when each PROC REPORT executes. I guess this could be overcome by reading the data into memory using the SASFILE STATEMENT.

SYNTAX 2

PROC REPORT DATA = SHOES OUT = TESTING SPLIT = '*' NOWD ;
	COLUMNS SUBSIDIARY SALES;
	DEFINE SUBSIDIARY 	  / GROUP;
	DEFINE SALES 		  / SUM;
RUN;

Any opinions\insight would be greatly appreciated.

Thanks

Scott Mitchell

Re: Populating All Missing Variables With the Right Most Non-Missing Value

Scott_Mitchell — Wed, 30 May 2018 05:27:05 GMT

Thanks again @s_lassen.

I appreciate the rigor and additional consideration you have given my problem.

Re: Importing CSV Files From Record N

Scott_Mitchell — Tue, 29 May 2018 01:43:16 GMT

You are right Reeza and that is 100% why it was used in this context.

We have no control over the file structure and it tends to change at a whim. I think Tom's solution still allows me to account for the fluidity.