topic Re: PROC SQL Join working when executed in Editor, but not when %included in SAS Programming

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

Fabian — Thu, 31 Jul 2014 07:11:47 GMT

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

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

Scott_Mitchell — Thu, 31 Jul 2014 07:21:27 GMT

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

What error are you getting?

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

Fabian — Thu, 31 Jul 2014 07:35:22 GMT

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

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

Scott_Mitchell — Thu, 31 Jul 2014 07:51:17 GMT

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.

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

Fabian — Thu, 31 Jul 2014 08:03:49 GMT

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.

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

RW9 — Thu, 31 Jul 2014 08:22:23 GMT

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.

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

Fabian — Thu, 31 Jul 2014 08:45:58 GMT

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:

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

Data table;

set tmp.table;

RUN;

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

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

RW9 — Thu, 31 Jul 2014 08:53:09 GMT

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.

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

gergely_batho — Thu, 31 Jul 2014 13:35:29 GMT

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>';