topic Re: Proc SQL - Merge 4 tables into 1 in SAS Programming

Proc SQL - Merge 4 tables into 1

Prateek1 — Mon, 01 Aug 2016 07:26:27 GMT

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.

Re: Proc SQL - Merge 4 tables into 1

Reeza — Mon, 01 Aug 2016 07:28:43 GMT

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.

Re: Proc SQL - Merge 4 tables into 1

LinusH — Mon, 01 Aug 2016 07:59:18 GMT

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.

Re: Proc SQL - Merge 4 tables into 1

JohnHoughton — Mon, 01 Aug 2016 08:03:36 GMT

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

Re: Proc SQL - Merge 4 tables into 1

RW9 — Mon, 01 Aug 2016 08:51:47 GMT

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;

Re: Proc SQL - Merge 4 tables into 1

Kurt_Bremser — Mon, 01 Aug 2016 08:52:42 GMT

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.

proc sql

Prateek1 — Mon, 01 Aug 2016 12:14:50 GMT

hi,

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

Re: proc sql

RW9 — Mon, 01 Aug 2016 12:24:37 GMT

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.

Re: proc sql

Reeza — Mon, 01 Aug 2016 12:33:10 GMT

@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#n1tgk0uanvisvon1r26lc036k0w7.htm