Can you please, say me what is wrong in this code:
libname test "C:\Temp";
%macro checkfk(pkTab, pk, fkTab, fk, out=results, print=no);
select name into :tmp separated by ' ' from TRIM("test."||&fkTab)
where TRIM("test."||&fk) in (TRIM("test."||&pk));
293 libname test "C:\Temp";
NOTE: Libref TEST was successfully assigned as follows:
Physical Name: C:\Temp
295 %macro checkfk(pkTab, pk, fkTab, fk, out=results, print=no);
296 %let tmp='';
297 proc sql;
298 select name into :tmp separated by ' ' from TRIM("test."||&fkTab)
299 where TRIM("test."||&fk) in (TRIM("test."||&pk));
301 %mend checkfk;
304 %checkfk(pkTab=Sex, pk=Names.sex_id, fkTab=Names, fk=Sex.sex_id, out=results, print=yes);
NOTE: Line generated by the invoked macro "CHECKFK".
1 select name into :tmp separated by ' ' from TRIM("test."||&fkTab)
1 ! where TRIM("test."||&fk) in (TRIM("test."||&pk)); quit;
ERROR 79-322: Expecting a SELECT.
ERROR 76-322: Syntax error, statement will be ignored.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
There are 2 tables:
Table Names with 3 columns name_id, name, sex_id
Table Sex with 2 columns sex_id, sex
The trim() function doesn't belong, the SQL processor won't recognise its function.
If you want to create a name by concatenating a string to a macro value, you won't need double quotes unless it is a value being inserted into a variable.
The syntax breaks the Zender rule 1 of macro creation by not starting with valid SAS code. PK might eventually resolve to something like TEST.SEX.SEXID which as a three level name is valid for a catalog entry, but not for a table or column. The same issue exists with your use of PK.
So, create valid SQL code without any macro symbols and then gradually replace them one by one.
I hope that your source table will have column names in it as Dictionary.Columns has or this will break again.
David -- thanks, you're so kind to give me my own rule. ;-) I admit, I am rather opinionated about the "starting with working SAS code" rule. However, based on "mumble-ty mumble" years of SAS and Macro programming, the rule has NEVER failed to produce good results.
Ihor -- This is not an ODS or BASE Reporting procedure (PRINT, REPORT, TABULATE) question. And I was going to suggest that you contact Tech Support for help. But I was also going to suggest what David has already suggested: start with a working SAS program. Before you contact Tech Support with a macro program problem, you owe it to yourself to be able to produce a working SAS program that is the equivalent of the code you want to generate. I cannot stress this too much. The SAS Macro facility is like a BIG typewriter -- the only purpose of the SAS Macro facility is to make your life easy by generating code for you as an alternative to you typing code.
SAS usually gives me an error message if I have a 3 level name:
test.sex.sex_id or test.names.sex_id
Generally, I need to do this:
from test.Names as a, test.Sex as b
where a.sex_id = b.sex_id
However, the other issue I see is that the working SQL shown does not match, at all, the SQL (with the separated by) inside the first macro program. So there is still a disconnect between what has just been posted and the original macro program.
I still maintain that Ihor's best bet for help is to contact Tech Support, especially since this is the forum for ODS and Base SAS Reporting procedures (PRINT, REPORT, TABULATE) and NOT SAS PROC SQL or SAS Macro.
This is the point at which you really need to contact Tech Support. To find out how to open a track with Tech Support, go to: http://support.sas.com and on the left-hand navigation area, click on the link entitled "Submit a Problem".