1

I have a SAS program that is called through VBA. Is it possible to build in code that checks if there were any errors in the SAS log and then, if there were, export them to the Excel sheet from where the code is called?

EDIT: Suppose I have a SAS macro named %MACRO. then I would call: 

For i = 1 To N 
    OleSAS.Submit("%MACRO(inputs(i))")
Next i

Excel seems to run this irrespective of whether the "MACRO" macro contains errors. I would like to include functionality to output the SAS log in the case that there is an error in the "MACRO" macro

Community
  • 1
  • 1
User9132
  • 11
  • 3
  • If the SAS program creates a log file then that file can certainly be read from VBA. – John Coleman Dec 06 '15 at 13:42
  • I do not want to view the whole log file every time, only if there are errors. However, could you perhaps help me as to how to view the log file from VBA? It may be a good starting point – User9132 Dec 06 '15 at 13:53
  • The log file is a text file, you can process it as you would any other text file. – Reeza Dec 06 '15 at 14:15
  • A log file is a simple text file. They would usually have some sort of regular format where the start of each entry is a time stamp -- determine the structure of the file, open it, and parse it. See this for reading text files in VBA: http://stackoverflow.com/q/11528694/4996248 . VBScript has finer control over processes than VBA. In particular, it can run programs and then examine the return code. If this SAS macro returns some sort of error code perhaps VBScript can pick up on it. As a rule of thumb, any VBScript solution of a problem can be turned into a VBA solution. – John Coleman Dec 06 '15 at 14:17
  • 1
    Please edit your question and add: _1)_ the code calling the SAS program,_ 2)_ the settings (e.g. tools > references) you needed _3)_ a snipped of log and the type of information you would like to extract from it – Dirk Horsten Dec 06 '15 at 14:23
  • Unfortunately, I cannot post the actual code, however it is using OleSAS.submit statements. On the settings side - VB for Applications, Excel 14.0 Object Library, OLE Automation, Microsoft Forums 2.0 Object Library are ticked. Then, with regard to the log. I just want to see if there are any errors in the SAS code. It doesn't necessarily have to involve the log. Any other ideas would be appreciated? – User9132 Dec 06 '15 at 15:12
  • Explore the documentation for the object library which contains `OleSAS.submit`. Maybe that method has some sort of return value you can explore. Absent any code or details, no one here can really help you. – John Coleman Dec 06 '15 at 15:19
  • I apologise about the lack of details, I am sure you realise there are instances where code cannot be shared! However, perhaps I can add some generalized examples. Suppose I have a SAS macro named %MACRO. then I would call: for i = 1 To N OleSAS.Submit("%MACRO(inputs(i))" next i. Excel seems to run this irrespective of whether the "MACRO" macro contains errors. I would like to include functionality to output the SAS log in the case that there is an error in the "MACRO" macro. – User9132 Dec 06 '15 at 15:23
  • If `inputs` is a VBA array then `OleSAS.Submit("%MACRO(inputs(i))")` doesn't look right to me. Something like `OleSAS.Submit("%MACRO(" & inputs(i) & ")")` seems like it would make more sense. – John Coleman Dec 06 '15 at 16:32
  • It is a bit more complicated than that, but yes you are mostly correct :) – User9132 Dec 06 '15 at 17:01
  • Have you looked at: https://support.sas.com/documentation/cdl/en/hostwin/63285/HTML/default/viewer.htm#oleautofeedback.htm ? – Tim Williams Dec 06 '15 at 23:11

0 Answers0