hi,
this the code and i am not able to merge 4 tables into 1 table .i just wanted to combine 4 tables into 1.
proc sql;
create table case study2 as
select A.*,B.*,C.*,D.*
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
A.Customer_Postcode=B.Customer_Postcode=C.Customer_Postcode=D.Customer_Postcode
order by T.Pos_1;
quit;
error
xpecting a '.'.
ERROR 202-322: The option or parameter is not recognized and will be ignored.
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.
Also the table name you are creating ("case study") is not a valid sas name.
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;
Since (at least) Configuration, Post_code and Customer_Postcode are present in more than one input dataset, your
select A.*,B.*,C.*,D.*
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.
hi,
how to merge 4 csv files into 1 by proc sql ??
Hi,
You don't merge CSV files. CSV files are imported into SAS datasets, then those SAS datasets are combined following certain joining criteria, as given in the examples above.
@Prateek1 wrote:
hi,
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:
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.