Dear All,
I have written a proc statement in SAS as below:
proc sql;
create table var.ACXMSR_MKEPUBSUB as
select a.INDIV_ID, a.MSR_INDIV_ID, b.ROLE_CD, b.SUB_ORDER_NBR, b.AGENCY_CD, b.SUB_ORDER_DT, b.PAID_AMT, b.PROD_ID, b.CANCEL_DT, b.SOURCE_RECEIVED_CD, b.PAYMENT_DT,
b.PAYMENT_STATUS_CD, b.PAYMENT_TYPE_MKE_CD, b.ORDER_DOCUMENT_KEY_CD, b.CANCEL_TYPE_CD
from var.FF_ACX_MSR_ID10 a, ora1.FACT_MKE_PUB_SUBSCRIPTION b
where a.MSR_INDIV_ID = b.INDIVIDUAL_ID;
run;
In the where clause i have mentioned 2 data tables however like this i have 100 data tables as we are migrating from Oracle to SAS hence it is very tedious to keep writing this code again and again, also the data is huge 200+ GB hence i thought of using a macro.
Before the code proc sql, i am thinking of using % let statement like
% let newvar = name of dataset ;
%let newvar1 = name of other dataset;
i plan to call these in the proc sql statement, Please advice if this approach is correct or i should go for some other approach.
Your general approach should work, but it might be easier if you use proc SQL to create your macro variables as well.
Prepare a table that contains the names of your corresponding datasets A and B in separate columns (Tables_A_and_B below), then read that table to create the macro variables:
PROC SQL NOPRINT;
SELECT TableA, TableB INTO :TabA1-:TabA&sysmaxlong, :TabB1-:TabB&sysmaxlong
FROM Tables_A_and_B;
QUIT;
Then you can put your code into a macro in e.g. a WHILE loop where you increment an index, say &i, up to the total number of datasets you got, and can reference the two tables as &&TabA&i and &&TabB&i.
MIgration from Oracle to SAS does not sound me a good idea and those software are not equivalents. Migrating data tables is not feasible and not a good solution. Instead, you can keep your data in a database like Oracle or any other, and use SAS to analyse. If you need a free database, there are many alternatives like postgresql or mariadb.
https://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#titlepage.htm
Hi All,
Thanks for suggestion. As the client is moving (shutting down) Oracle completely and migrating to SAS hence no option there.
Hi Christian, would the above code work even if in case i have different variables on the tables.
Thanks.
Hi Shivi,
it depends on whether you want all variables from both tables or you are just interested in matching a selection of variables. In case you just want to merge two tables by a common ID variable, I would use a JOIN in proc SQL (assuming you use the macro variables for the tables):
PROC SQL NOPRINT;
CREATE TABLE var.NewTable AS
SELECT a.*, b.*
FROM var.&&TabA&i AS a, ora1.&&TabB&i AS b
WHERE a.ID_Var=b.ID_Var;
/* I am more used to the following way of doing this:
FROM var.&&TabA&i AS a INNER JOIN ora1.&&TabB&i AS b
ON a.ID_Var=b.ID_Var;
Both variants should produce the same result, though. */
QUIT;
With the * symbol you select all columns from a given table. Since you are going to perform a database migration you may want to keep all variables anyway, so that would be the easiest way to do it. If you only need a limted number of columns from both tables, you can again prepare the code in a table and put it into macro variables in the same way I showed with the table names above - in your example the macro variables would contain:
(a) a.INDIV_ID, a.MSR_INDIV_ID,
(b) b.ROLE_CD, b.SUB_ORDER_NBR, b.AGENCY_CD, b.SUB_ORDER_DT, b.PAID_AMT, b.PROD_ID, b.CANCEL_DT, b.SOURCE_RECEIVED_CD, b.PAYMENT_DT,b.PAYMENT_STATUS_CD, b.PAYMENT_TYPE_MKE_CD, b.ORDER_DOCUMENT_KEY_CD, b.CANCEL_TYPE_CD
and would be referenced similarly to the table names, e.g.
SELECT &&VarA&i &&VarB&i
Best regards
Christian
- Badges
- Science topic
- Similar topics
- Engineering
- Materials Engineering
- Powders