vba/sas - Proc import csv creates problems when SAS is run through VBA
I have written the following VBA code to automate SAS processes.
Rem Start the SAS server Dim SASws As SAS.Workspace Dim SASwsm As New SASWorkspaceManager.WorkspaceManager Dim strError As String Set SASws = SASwsm.Workspaces.CreateWorkspaceByServer _ ("MySAS", VisibilityProcess, Nothing, "", "", strError) Dim code_location As String, code_name As String, param_str As String Dim param_flag As Boolean code_location = "file:" & ThisWorkbook.Sheets("Control").Range("B2").Value code_name = ThisWorkbook.Sheets("Control").Range("C2").Value param_flag = ThisWorkbook.Sheets("Control").Range("C4").Value If param_flag = True Then param_str = ThisWorkbook.Sheets("Control").Range("D5").Value Else: param_str = "ds=Sasuser.Export_output" End If Rem Run the stored process Dim SASproc As SAS.StoredProcessService Set SASproc = SASws.LanguageService.StoredProcessService SASproc.Repository = code_location SASproc.Execute code_name, "ds=Sasuser.Export_output" 'SASproc.Repository = "file:C:\Duopa_Repository\SAS_Codes\Weekly" 'SASproc.Execute "weekly_refresh_run.sas", "ds=Sasuser.Export_output" Rem Shut down the SAS server SASwsm.Workspaces.RemoveWorkspaceByUUID SASws.UniqueIdentifier SASws.Close Set SASws = Nothing
All the SAS codes seem to work through this except the ones that import csv files using proc import method. Can someone please explain me why
Thanks in advance
See also questions close to this topic
How to link a ComboBox value with a different value and put that value into a cell in a worksheet
I have an excel workbook. Within that work book I have one sheet named Classes and I have another sheet called Homework. In the Classes sheet I have the different classes in column A I also have the class hours in column B. In the Homework worksheet I have the homework assignment names in column A. I have a UserForm that has a TextBox, ComboBox and a command button. In the TextBox I am supposed to type the assignment name. The combobox is populated with the classes that are in column A of the Classes worksheet. I want to be able to type in the homework assignment and select a class within the combobox and then click the command button and this will put the homework assignment in column A of the Homework worksheet and then put the class hour (Ex. 1,2,3,4,5 not the class name) in column B of the Homework worksheet. I dont know how to link the classes that are populated in the Combobox with the class hours so that when I choose a class it will then put the class hour into a cell within the Homework worksheet.
How to prevent UiPath from getting stuck in Call Macro Stage?
I am calling a macro using call macro in UiPath Workflow. The concern is some time one of the excels that the macro handles will freeze forever, Causing the macro execution to hang and the process will get stuck in Call macro forever.
Is there some way I can modify the UiPath workflow so that, if the Call macro activity is not completed within 15 minutes, the bot throws an exception?
Scraping web with url in milliseconds Unix
The web in which I browse has several pages. I want to click on those elements or play with urls and thus be able to copy the data. The initial URL ends at = 1 & playerType = ALL & ts = 1558502019375, in my code there is a loop that is supposed to go page after page to get the data but I can not get it done.
Sub UPDATE_DATA_MLB() Application.ScreenUpdating = False 'dimension (set aside memory for) our variables Dim objIE As InternetExplorer Dim ele As Object Dim y As Integer Dim EstaPagina As Byte Dim EstaURL As String 'Página inicial EstaPagina = 1 'we will output data to excel, starting on row 1 y = 1 EstaURL = "http://mlb.mlb.com/stats/sortable.jsp#elem=%5Bobject+Object%5D&tab_level=child&click_text=Sortable+Player+hitting&game_type='R'&season=2018&season_type=ANY&league_code='MLB'§ionType=sp&statType=hitting&page=1&playerType=ALL&ts=1558502019375" '&ts=1526432697176" 'start a new browser instance Set objIE = New InternetExplorer 'make browser visible objIE.Visible = False Do Until EstaPagina = 255 'navigate to page with needed data objIE.navigate EstaURL & EstaPagina 'wait for page to load Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop 'If UCase(Left(EstaURL, 211) & "1") = UCase(Left(objIE.LocationURL, (211 + Len(EstaPagina)))) And y > 1 Then Exit Do 'look at all the 'tr' elements in the 'table' with id 'myTable', 'and evaluate each, one at a time, using 'ele' variable For Each ele In objIE.document.getElementById("datagrid").getElementsByTagName("tr") 'show the text content of 'tr' element being looked at 'Debug.Print ele.textContent 'each 'tr' (table row) element contains 4 children ('td') elements 'put text of 1st 'td' in col A Sheets("Sheet1").Range("A" & y).Value = ele.Children(0).textContent 'put text of 2nd 'td' in col B Sheets("Sheet1").Range("B" & y).Value = ele.Children(1).textContent 'put text of 3rd 'td' in col C Sheets("Sheet1").Range("C" & y).Value = ele.Children(2).textContent 'put text of 4th 'td' in col D Sheets("Sheet1").Range("D" & y).Value = ele.Children(5).textContent 'put text of 4th 'td' in col f Sheets("Sheet1").Range("E" & y).Value = ele.Children(22).textContent 'increment row counter by 1 y = y + 1 Next EstaPagina = EstaPagina + 1 Loop lobjIE.Quit Set objIE = Nothing Set ele = Nothing Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5), _ Header:=xlNo Application.ScreenUpdating = True MsgBox "Volcado terminado", vbInformation Range("A1").Select 'save the Excel workbook ActiveWorkbook.Save End Sub ```
VBA: Loop through an array, find how many values are matching and display the result as percentage in a cell
- What I have so far
The code below, which I got from my original request here: Finding if a cell values (delimited by comma) are all existing in a defined table
Option Explicit Sub LE_Checker() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.DisplayStatusBar = False Application.EnableEvents = False Dim RunValidation As Integer RunValidation = MsgBox("Are you sure?", vbYesNo, "Checking LE Matches") If RunValidation = vbYes Then Dim ws1 As Worksheet, ws2 As Worksheet Dim LastRowA As Long, i As Long, y As Long Dim arr As Variant Dim CountryCode As String Dim rng As Range, SearchRange As Range, FindPosition As Range Dim Appears As Boolean Dim Suppliers As Long 'Set worksheets on variables With ThisWorkbook Set ws1 = .Worksheets("Sheet1") Set ws2 = .Worksheets("Sheet1") ' For testing purpose I'm gonna use the same sheet End With 'Set the range to search in for country codes Set SearchRange = ws2.Range("I2:K2") With ws1 'Find the last row of Column A Sheet1 LastRowA = .Cells(.Rows.Count, "A").End(xlUp).Row ' Start loop from row 2 to last row Sheet1 For i = 2 To LastRowA 'Criteria needed: Content Groups: not empty / Region: EUROPE / Old Supplier: No (case sensitive) If .Range("A" & i).Value <> "" And .Range("C" & i).Value = "EUROPE" And .Range("D" & i).Value = "No" Then CountryCode = .Range("B" & i).Value 'In which column is the country code found Set FindPosition = SearchRange.Find(What:=CountryCode, LookIn:=xlValues, LookAt:=xlWhole) 'If country code exists If Not FindPosition Is Nothing Then 'Set the range to search for the groups in the column where the code is header Set rng = ws2.Range(ws2.Cells(2, FindPosition.Column), ws2.Cells(ws2.Cells(ws2.Rows.Count, FindPosition.Column).End(xlUp).Row, FindPosition.Column)) 'Split the string with comma and assing it on arr arr = Split(.Range("A" & i).Value) Appears = False 'Loop the arr For y = LBound(arr) To UBound(arr) 'Check if the arr(y) start from C as all code start from C If Left(arr(y), 1) = "C" Then 'Count how many times the arr(y) with out the comma appears in the rng If Application.WorksheetFunction.CountIf(rng, Replace(arr(y), ",", "")) > 0 Then 'If appears the variable Appears is true Appears = True Else 'If does not appear the variable Appears is False & Exit the loop Appears = False Exit For End If End If Next y 'Check Appears variable status and import value in Column C If Appears = True Then .Range("E" & i).Value = "Yes" Suppliers = Suppliers + 1 Else .Range("E" & i).Value = "No" End If End If End If Next i End With MsgBox "Done!" & Chr(13) & "Found " & Suppliers & " suppliers" End If Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Application.DisplayStatusBar = True Application.EnableEvents = True End Sub
The code will loop through a cell's values and find if all of them match the values defined in a table for the country code specified. If all codes match what's in the table = YES, otherwise = NO.
Like in the picture below:
- What I need:
An extension of this code, basically to check in that array loop (column A), in addition to what it does now, how many values are matching. Then return it in next column (F) as percentage (I need to identify the partial ones that have at least 50% codes in)
For example: If a supplier has 2/3 codes matching the country he's from, then show in column F 66.6%, if a supplier has 1/4 codes matching show 25% and so on.
- Expected result:
Google Map Scrapping
I have a list of companies and I want scrap all the companies address from google map using vba. I have tried to do it with some of the codes of vba but still its not working. Can anyone guide me please. I will really appreciate you for this. You can contact me @ email@example.com
Find With VBA DOCX File in Folder and Add It To eMail
I created code that is searching certain files inside a folder with dynamic path, then add it to an email and sendes it. I want that in every email I send inside the loop to will be specific DOCX file too. but I dont want to search for specific name, i want that it just will find the only DOCX in this folder and add it as an attachment.
i n my code its not working well, the part with the DOCS file. Here is the relevant part of the code:
'*****************************************EMail from here Dim outlookApp As Outlook.Application Dim myMail As Outlook.MailItem Dim source_file, to_emails As String Dim WordFile As String Dim i, j As Integer '---------here i find the file, I tried that it will search for the only docx in the folder WordFile = Dir(path & "\*.docx") Do While Len(WordFile) > 0 WordFile = Dir Wend For i = 2 To LastManaRow 'find manager in mailing list With ListSheet ManaNumber = .Cells(i, 1).Value End With With EmailWS j = .Range(.Cells(2, 1), .Cells(LastMailRow, 1)).Find(ManaNumber, , xlValues, xlWhole).Row to_emails = .Cells(j, 3).Value End With Set outlookApp = New Outlook.Application Set myMail = outlookApp.CreateItem(olMailItem) FileToSendName = ManaNumber & " " & PartFileName source_file = path & "\" & FileToSendName & ".xlsx" '-----here i give the excel file variable name MainWB.Worksheets("Activation").Cells(1, 5) = WordFile MsgWS.Cells(1, 5) = FileToSendName MsgWS.Range("H1") = source_file With myMail .Attachments.Add source_file .Attachments.Add WordFile ' ------ERROR because the variable has no value .To = to_emails .Subject = MainWB.Worksheets("MSG").Range("B1").Value .Body = BodyMsg .Send End With Next i
Limiting SPARSE to a single variable in PROC FREQ
I have a set HAVE with three variables: one for STATE, INCUB (an upper bound for income based on Medicaid eligibility), and POV, a poverty indicator. Each state only has a few possible values for INCUB, but there are many more possible values across states. I’d like to calculate, within each state, the proportion of each INCUB level who have a positive POV indicator. If I do this:
PROC FREQ data=HAVE; table STATE*INCUB*POV / outpct sparse out=WANT; run;
then I get ALL possible levels of INCUB for each state, even the levels never present for that state. However, I need to use the SPARSE option here since some state-INCUB combos will have all 0’s or all 1’s in the POV indicator.
So, my actual question: how do I get PROC FREQ to “SPARSE” for only one of the variables (i.e the POV variable and NOT the INCUB variable)?
what is the difference between value and invalue statements in SAS?
i am studying for the SAS base exam, i come from medical background. I intend to learn SAS clinical. what is the difference between invalue and value statements ?
How to export 2 datasets in to excel “Sheet1”and another set of 2 datasets into “Sheet2” using SAS
Good Morning! I want to export a few dataset's to excel using SAS, But I have a few challenges here. Both datasets are identical and which we cannot merge or append.
There are 2 programs that create 2 dataset's each, however, I want to export program1 output datasets(2 datasets) to excel sheet1 and Program2 output dataset'(2 datasets) to Excel sheet2.
I tried using Proc print but it didn't work
ods excel file="&OUTFILE." options(sheet_interval="none" sheet_name="sheet1"); TITLE 'CLOSED_SR_VOLUMES_BY_PERCENT'; proc print data=CLOSED_SR_VOLUMES_BY_PERCENT; run; TITLE 'CLOSED_SR_VOLUMES_BY_VOLUME'; proc print data=CLOSED_SR_VOLUMES_BY_MONTH; run; ods excel options(sheet_interval="none" sheet_name="sheet2"); TITLE 'ACTIVE_SR_VOLUMES_BY_VOLUME'; proc print data=SR_VOLUMES_BY_MONTH; run; TITLE 'ACTIVE_SR_VOLUMES_BY_PERCENT'; proc print data=SR_VOLUMES_BY_PERCENT; run; ods excel close;
I am not getting any error but all the datasets are exporting it to one excel sheet instead 2 datasets into sheet1 and another 2 datasets into Sheet2.
Please let me know if there any way to this...