morinia
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.
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:
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 ¯ox
);
quit;
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')
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 ¯ox;
call symput("¯ox", Textrecord); /*change here*/
put Textrecord;
RUN;
%MEND;
%Rep(1,memids1);
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
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
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.
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
If its another query, then read it into a VB array and call your SQL code using the array
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Its real easy to read/write data from the excel spreadsheet
variable = Cells(rowIndex, columnIndex)
or
Cells(rowIndex),columnInde x) = 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>)
variable = Cells(rowIndex, columnIndex)
or
Cells(rowIndex),columnInde
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>)