Emulating sql macros in VB


I have a query in an Oracle that runs in SAS.  However, the query is using oracle language.
Proc SQL;
	  SELECT * From connection to EDS (
		SELECT T1.CLAIMNUMBER               

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:

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;
DATA test2;
set codes_vars;
  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))||"'"||")";
            IF TextRecord ~= "" THEN /* Not first element of TextRecord */
                 TextRecord = TRIM(TextRecord)||"'"||TRIM(Col(i))||"'"||',';
            ELSE TextRecord = "("||"'"||TRIM(Col(i))||"'"||',';
/*TextRecord = TRIM(TextRecord)||")";*/
%global &macrox;
call symput("&macrox", Textrecord); /*change here*/
      put Textrecord;

Open in new window

moriniaAdvanced Analytics AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aloysius LowCommented:
sorry i'm not catching you - what exactly do you wish to be done in VB?
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
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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
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.
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)
    '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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gerald ConnollyCommented:
Its real easy to read/write data from the excel spreadsheet

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

Just use a loop and and a array variable to get the data from your spreadsheet.
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>)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.