PROC SQL Join working when executed in Editor, but not when %included

[Fabian]

Hi everyone,

I'm facing the following problem. All I want to do is joining a table on another table:

DATA table;

  INPUT  KEY $ JUST_SOMETHING;

  CARDS ;

  03010 654

  03020 54

  03030 4545

  03040 5465

  03050 45464

  03060 54645

  03070 4565466

RUN;

DATA joinTable;

  INPUT  KEY $ COL;

  CARDS ;

  03010 99

  03020 99

  03030 99

  03040 99

  03050 99

  03060 99

  03070 99

  03080 99

  03090 99

  04010 1

  04020 4

  04025 4

  04030 2

  04040 3

  04050 5

  04060 1

  04210 1

  04220 4

  04230 2

  04240 3

  04250 5

  04260 1

  04270 1

  04310 5

  04320 6

  04350 6

  04410 99

  04420 99

  04430 99

  04440 99

  04450 99

  04610 1

  04620 4

  04621 4

  04630 2

  04631 2

  04640 3

  04650 5

  04655 5

  04660 1

  04670 1

  04680 4

  04690 2

  04700 3

  04710 5

  04720 6

  04730 5

  04800 99

  04810 99

  04850 99

  04910 6

  04920 6

  04930 6

  04940 6

  08050 99

  08060 99

  09009 99

  09010 99

RUN;

PROC SQL;

  CREATE Table resultTable as

  SELECT tab.*, joi.COL FROM table as tab

  LEFT JOIN joinTable as joi

  ON tab.KEY = joi.KEY;

QUIT;

This works fine as long as I execute it alltogether. But when pasting the second 2 commands into another file (test.sas) and including it, the join does not seem to work.

DATA table;

  INPUT  KEY $ JUST_SOMETHING;

  CARDS ;

  03010 654

  03020 54

  03030 4545

  03040 5465

  03050 45464

  03060 54645

  03070 4565466

RUN;

%include "&inc\test.sas";

Any ideas?

Fabian

I'm using SAS 9.2 TS Level 2M3

[Scott_Mitchell]

I used %INCLUDE "F:\SAS CODE\SAS SAMPLE CODE\INCLUDE TEST.SAS"; and it ran successfully. 

What error are you getting?

[Fabian]

I just tried to include the absolute path.

DATA table;

  INPUT  KEY $ JUST_SOMETHING;

  CARDS ;

  03010 654

  03020 54

  03030 4545

  03040 5465

  03050 45464

  03060 54645

  03070 4565466

RUN;

%include "G:\temp\TEST.SAS";

This didn't solve the problem. It's interesting, that there is no error Message (that's why it took me some time to identify the problem, though):

2292

2293

2294  DATA table;

2295      INPUT  KEY $ JUST_SOMETHING;

2296      CARDS ;

NOTE: The data set WORK.TABLE has 7 observations and 2 variables.

NOTE: Compressing data set WORK.TABLE increased size by 100.00 percent.

      Compressed is 2 pages; un-compressed would require 1 pages.

NOTE: DATA statement used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

2304  RUN;

2305

2306  %include "G:\temp\TEST.SAS";

NOTE: The data set WORK.JOINTABLE has 58 observations and 2 variables.

NOTE: Compressing data set WORK.JOINTABLE increased size by 100.00 percent.

      Compressed is 2 pages; un-compressed would require 1 pages.

NOTE: DATA statement used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

NOTE: Compressing data set WORK.RESULTTABLE increased size by 100.00 percent.

      Compressed is 2 pages; un-compressed would require 1 pages.

NOTE: Table WORK.RESULTTABLE created, with 7 rows and 3 columns.

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.01 seconds

      cpu time            0.01 seconds

The resulting table also shows the joined column (COL), but it is empty:

03010   654    .

03020    54     .

etc.

I'm expecting a table (which I'm getting when exectuting the code without %include):

03010    654   99

03020      54   99

Thanks

Fabian

[Scott_Mitchell]

Add options source2; to the top of the code that contains your include.  Check your log to see if your SQL code is reflective of what you have in your test.sas file.

[Fabian]

Log with source2 option looks fine for me, see below, but still no success.

%includes normally work fine, it only happens with PROC SQL Joins (at least thats my impression so far).

I just tried to replace the "=" by "eq" within the PROC SQL step, but without success.

I could replicate the problem on another computer.

Can you replicate it as well or does it work for you?

5597

5598  options source2;

5599

5600  DATA table;

5601      INPUT  KEY $ JUST_SOMETHING;

5602      CARDS ;

NOTE: The data set WORK.TABLE has 7 observations and 2 variables.

NOTE: Compressing data set WORK.TABLE increased size by 100.00 percent.

      Compressed is 2 pages; un-compressed would require 1 pages.

NOTE: DATA statement used (Total process time):

      real time           0.01 seconds

      cpu time            0.01 seconds

5610  RUN;

5611

5612  %include "G:\temp\TEST.SAS";

NOTE: %INCLUDE (level 1) file G:\temp\TEST.SAS is file G:\temp\TEST.SAS.

5613 +

5614 +DATA joinTable;

5615 + INPUT  KEY $ COL;

5616 + CARDS ;

NOTE: The data set WORK.JOINTABLE has 58 observations and 2 variables.

NOTE: Compressing data set WORK.JOINTABLE increased size by 100.00 percent.

      Compressed is 2 pages; un-compressed would require 1 pages.

NOTE: DATA statement used (Total process time):

      real time           0.00 seconds

      cpu time            0.01 seconds

5675 +RUN;

5676 +

5677 +PROC SQL;

5678 + CREATE Table resultTable as

5679 + SELECT tab.*, joi.COL FROM table as tab

5680 + LEFT JOIN joinTable as joi

5681 + ON tab.KEY = joi.KEY;

NOTE: Compressing data set WORK.RESULTTABLE increased size by 100.00 percent.

      Compressed is 2 pages; un-compressed would require 1 pages.

NOTE: Table WORK.RESULTTABLE created, with 7 rows and 3 columns.

5682 +QUIT;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.01 seconds

      cpu time            0.01 seconds

NOTE: %INCLUDE (level 1) ending.

[RW9]

Hi,

I could not replicate this (though am using 9.3).  Could I suggest that you open a new session, no autoexec startups etc., ensure the work directory is empty, add all the output options (shouldn't make a diff) options mlogic mprint symbolgen, then run it with full include path.  If the output file still has it, then I would suggest at support desk ticket, or you could try moving the SQL part back to you main program, and then the dataset and just leave the sql in include.  TBH I see no reason why this would happen.  All I can think of is that the processes that SQL is doing in the background is being done in a different place to when it is run interactively, and you don't have permissions there, hence it fails to create a merged part.

[Fabian]

Thanks. I started a new session, work is empty, output options on, Include with full path. No success and still no error message.

I the meantime I also tried a few more things. The problem disappears when both tables are either initialized in the main (and the proc sql stays in the include) or all 3 steps are in the include file. This means that the problem only emerges, when the base and join tables are initialized in different scripts (which is something I do quite often).

Some other workarounds that didn't work:

1)

Data table;

     set table;

RUN;

At the beginning of the included script (to reset the table within the script) --> no success

2) Saving the tables not in the work-directory --> no success

3)

Data table;

  set tmp.table;

RUN;

Saving the tables not in the work-directory -> Loading them to work directory within inlcuded script --> no success

[RW9]

Personally I think that looks like a scope issue in the pre-processor.  As for how to fix it I am not sure.  Is SAS locally installed?  I would suggest your SAS people if not locally or support ticket.

Sorry I can't be more help.

[gergely_batho]

Maybe the encoding of you SAS session and encoding of your file is different? When you look into table and joinTable do you see tha same key values?

Try:

%include "c:\temp\test.sas" /encoding='<encoding fo the file>';