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
I used %INCLUDE "F:\SAS CODE\SAS SAMPLE CODE\INCLUDE TEST.SAS"; and it ran successfully.
What error are you getting?
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
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.
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.
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.
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
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.
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>';
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: