Link to home
Start Free TrialLog in
Avatar of morinia
moriniaFlag for United States of America

asked on

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

Avatar of Aloysius Low
Aloysius Low
Flag of Singapore image

sorry i'm not catching you - what exactly do you wish to be done in VB?
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
Avatar of morinia

ASKER

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.
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
Avatar of morinia

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of aikimark
aikimark
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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>)