08-01-2016 03:23 AM - last edited on 08-01-2016 03:26 AM by Reeza
this the code and i am not able to merge 4 tables into 1 table .i just wanted to combine 4 tables into 1.
create table case study2 as
FROM T.Pos_1 as A,T.Pos_2 as B,T.pos_3 as C,T.pos_4 as D
WHERE A.Configuration=B.Configuration=C.Configuration=D.Configuration and A.Store_Postcode=B.Post_code=C.Post_code=D.Post_code and
order by T.Pos_1;
xpecting a '.'.
ERROR 202-322: The option or parameter is not recognized and will be ignored.
08-01-2016 03:28 AM
Your where conditions are incorrect. For starters you should have only one equal sign.
You should specify the join type and use ON instead of Where. It's usually faster.
08-01-2016 04:51 AM
In addition to whats been said above, code formatting is very important to other people who might read your code - in fact probably more important that the code itself nowadays. Consistent indetation, casing and such like makes reading far easier. Also I would add that using the * notation is probably not a good idea, this means take all variables from that table - which may work or you may get variables in multiple datasets, or you may have different variables next time. For instance if your code runs, then you will get warnings as CONFIGURATION is in at least A and B.
To make your code clearer and easier to maintain (also more efficient), specify what variables are to be taken from each table. If they are to be combined, use COALESCE() function.
proc sql; create table CASE_STUDY2 as select COALESCE(A.CONFIGURATION,B.CONFIGURATION) as CONFIGURATION, COALESCE(A.STORE_POSTCODE,B.POST_CODE,C.POST_CODE,D.POST_CODE) as POST_CODE from T.POS_1 as A, T.POS_2 as B, T.POS_3 as C, T.POS_4 as D where A.CONFIGURATION=B.CONFIGURATION and C.CONFIGURATION=D.CONFIGURATION and A.STORE_POSTCODE=B.POST_CODE and C.POST_CODE=D.POST_CODE and A.CUSTOMER_POSTCODE=B.CUSTOMER_POSTCODE and C.CUSTOMER_POSTCODE=D.CUSTOMER_POSTCODE order by T.POS_1; quit;
08-01-2016 04:52 AM
Since (at least) Configuration, Post_code and Customer_Postcode are present in more than one input dataset, your
will also cause an ERROR by SQL.
I suggest you start easy by joining 2 tables, and once that is solved, add more tables into the mix.
Less code from the beginning will make it easier for you to make sense of the ERROR messages and debug this.
08-01-2016 08:33 AM
how to merge 4 csv files into 1 by proc sql ??
1. Import data into SAS
2. Define how you want to combine files
See the examples here: