DATA Step, Macro, Functions and more

Proc SQL - Merge 4 tables into 1

Reply
Contributor
Posts: 50

Proc SQL - Merge 4 tables into 1

[ Edited ]

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.

Super User
Posts: 17,864

Re: Proc SQL - Merge 4 tables into 1

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. 

 

Super User
Posts: 5,257

Re: Proc SQL - Merge 4 tables into 1

You need to insert an AND between each pair of join conditions, you can't combine them the way you do, even if you think they are related.
Data never sleeps
Contributor
Posts: 39

Re: Proc SQL - Merge 4 tables into 1

Also the table name you are creating ("case study") is not a valid sas name.

Super User
Super User
Posts: 7,407

Re: Proc SQL - Merge 4 tables into 1

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;
Super User
Posts: 6,946

Re: Proc SQL - Merge 4 tables into 1

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 50

proc sql

hi,

how to merge 4 csv files into 1 by proc sql ??

 

Super User
Super User
Posts: 7,407

Re: 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.

Super User
Posts: 17,864

Re: proc sql


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:

http://support.sas.com/documentation/cdl/en/lrcon/68089/HTML/default/viewer.htm#n1tgk0uanvisvon1r26l...

 

 

Ask a Question
Discussion stats
  • 8 replies
  • 1303 views
  • 4 likes
  • 6 in conversation