Merging the two data sets

Reply
Occasional Contributor
Posts: 5

Merging the two data sets

Hi Guys,

I have one query in SAS .I have two EXCEL .CSV Files and importing in sas,One file contains 11 columns and 169252 records.another file contains 60096 Records 29 columns...these are two data sets are merging based on Item_Code......using this code

DKG_2009_10

Item_Master_New

these are two data file are importing in sas from excel

USING THIS CODE:

proc sort data=VLOOK.Item_Master_New nodupkey;

by Item_Code;

run;

proc sql;

  create table vlook.Merge as

     select VLOOK.DKG_2009_10.Item_Code,Sub Category_Code

         from VLOOK.DKG_2009_10,VLOOK.Item_Master_New

           where VLOOK.DKG_2009_10.'Item_Code'=VLOOK.Item_Master_New.'Item_Code';

quit;

ERRORS OCCURED:

9385  proc sql;

9386    create table vlook.Merge as

9387       select VLOOK.DKG_2009_10.Item_Code,Sub Category_Code

                                   -

                                   22

                                   76

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, *, **, +, ',',

              -, /, <, <=, <>, =, >, >=, ?, AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE,

              GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN, LENGTH, LET, LIKE, LT, LTT, NE,

              NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

9388           from VLOOK.DKG_2009_10,VLOOK.Item_Master_New

9389             where VLOOK.DKG_2009_10.'Item_Code'=VLOOK.Item_Master_New.'Item_Code';

9390  quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.01 seconds

      cpu time            0.00 seconds

any one can help me

Super Contributor
Posts: 644

Re: Merging the two data sets

You have confused SAS by not adhering to the naming conventions for columns in a table.  From your code it seems that you imported the data into 2 SAS tables VLOOK.DKG_2009_10 and VLOOK.Item_Master_New.  This should mean that you have assigned a SAS Library VLOOK and stored the 2 tables there.

In a Proc SQL join you would refer to columns of these tables using an alias for each table so I would expect the code to look something like this

proc sql;

  create table vlook.Merge as

     select dkg.Item_Code

              ,  mstr.Sub Category_Code

         from VLOOK.DKG_2009_10 dkg

              ,  VLOOK.Item_Master_New  mstr

           where dkg.Item_Code=mstr.Item_Code

          ;

quit;

Allowing for my preference for putting commas at the start of lines and not running too much code into one line.  Note the removal of quotes around the references to Item_Code.  Quotes in that position will generate an error (unless you are defining SAS literals, which you don't need to know about at this stage).

Richard in NZ

Ask a Question
Discussion stats
  • 1 reply
  • 177 views
  • 0 likes
  • 2 in conversation