BookmarkSubscribeRSS Feed
Prateek1
Obsidian | Level 7

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.

8 REPLIES 8
Reeza
Super User

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. 

 

LinusH
Tourmaline | Level 20
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
JohnHoughton
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Kurt_Bremser
Super User

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.

Prateek1
Obsidian | Level 7

hi,

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

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Reeza
Super User

@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...

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 3616 views
  • 4 likes
  • 6 in conversation