Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

Emulating sql macros in VB

Experts,

I have a query in an Oracle that runs in SAS.  However, the query is using oracle language.
Proc SQL;
	  CONNECT TO ORACLE AS EDS(user=USERID pw=PW PATH='PATH');
	  CREATE TABLE CLAIMS AS
	  SELECT * From connection to EDS (
		SELECT T1.CLAIMNUMBER               
FROM CLAIMSODS.PRFCLAIM T1, 
WHERE T1.MEMBERID IN &macrox
);
quit;

Open in new window


This is the basic query.  The challenge is I have built macrox by reading a another table that lists a group of members.
macrox  is the following:
('22252279425','22254587925','22245252922','22874951422','21954879222','21972294222','21874587922','21787871129','22545929122','2254
4792729','29194752922','29287221922','29594187124','29592755422','24242224122','24274795125','24292197424','24518712522','2417587542
9','24287112524','24254875224','24978794522','28774227529','24745755125','24587771922','24554925522','24872142522','25242257422','25
287229725','25287297129')

Open in new window

This is the code used in SAS that creates the macro.   Can this be done in VB?
%MACRO Rep(fileno,macrox);
Proc transpose data=members&fileno out=codes_vars;
		Var memberid;
run;
DATA test2;
set codes_vars;
  ARRAY COL (*) COL:;
  format TextRecord $20000.; /*This textrecord will hold about 800 member_ids*/
TextRecord = "";
DO i = 1 to DIM(COL);
      IF COL(i) ~= "" THEN DO; /* Col is not missing */
	   IF i = DIM(COL) THEN
		       TextRecord = TRIM(TextRecord)||"'"||TRIM(Col(i))||"'"||")";
		 ELSE
            IF TextRecord ~= "" THEN /* Not first element of TextRecord */
                 TextRecord = TRIM(TextRecord)||"'"||TRIM(Col(i))||"'"||',';
            ELSE TextRecord = "("||"'"||TRIM(Col(i))||"'"||',';
      END;
END;
/*TextRecord = TRIM(TextRecord)||")";*/
%global &macrox;
call symput("&macrox", Textrecord); /*change here*/
      put Textrecord;
RUN; 
%MEND;
%Rep(1,memids1);

Open in new window

0
morinia
Asked:
morinia
1 Solution
 
Aloysius LowCommented:
sorry i'm not catching you - what exactly do you wish to be done in VB?
0
 
Gerald ConnollyCommented:
Are you planning on writing a VB app or are you going to use VBA in excel?

You can do what you want from either, it depends on what you want to do with the data once you have retrieved it.

Here is a random example of a web page that shows you how to do it http://www.excel-vba.com/vba-code-2-12-SQL.htm
0
 
moriniaAdvanced Analytics AnalystAuthor Commented:
I would like to fin the code in Excel instead of SAS if possible. This way more people will be able to run the program and produce simple adhoc reports. I am now able to run the query from excel using VB.

The problem I am having is I do not know how to create a temporary table when there I information coming from an external spreadsheet such as member dis and be able to read the Oracle table

In SAS I was able to create the macro which can be used as input in the query reading oracle.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Gerald ConnollyCommented:
Where is the data for the temp table coming from?

If its another query, then read it into a VB array and call your SQL code using the array
0
 
moriniaAdvanced Analytics AnalystAuthor Commented:
The  data from the temp table is on a spreadsheet. It is a list of IDs. If I could just get sample code that shows me how to read data from  columna on sheeta in excel and  create a temporary table to query against an oracle table that would work.  The documentation is not really clear.
0
 
aikimarkCommented:
This code assumes your list is in column A.
    Dim vData As Variant
    Dim strInList As String
    Dim strData() As String
    Dim lngLoop As Long
    
    'transfer data to array
    vData = ActiveSheet.Range(ActiveSheet.Range("A1"), ActiveSheet.Range("A1").End(xlDown)).Value
    
    'transfer data from 2D array to 1D vector
    ReDim strData(1 To UBound(vData))
    For lngLoop = 1 To UBound(vData)
        strData(lngLoop) = vData(lngLoop, 1)
    Next
    
    'make into a comma separated list of apostrophe-delimited items
    strInList = Join(strData, "','")
    strInList = "('" & strInList & "')"     'add start and end delimiters
    Debug.Print strInList

Open in new window

For further information, read:
A Better Concatenate Function: http:A_7811.html
Fast Data Push to Excel: http:A_2253.html
0
 
Gerald ConnollyCommented:
Its real easy to read/write data from the excel spreadsheet

variable = Cells(rowIndex, columnIndex)
   or
Cells(rowIndex),columnIndex) = variable

Just use a loop and and a array variable to get the data from your spreadsheet.
 or
You could just write a VBA function which contains your SQL which you can use in your spreadsheet to retrieve info for the id's you have listed using normal formula rules. e.g Cell A1 = "42", Cell B1 = "=mygetfromSQL(a1, <fieldnumber>)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now