topic Re: select into doesn't work in ODS and Base Reporting

select into doesn't work

deleted_user — Fri, 16 Nov 2007 11:27:23 GMT

Hi gyes.

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);
%let tmp='';
proc sql;
select name into :tmp separated by ' ' from TRIM("test."||&fkTab)
where TRIM("test."||&fk) in (TRIM("test."||&pk));
quit;
%mend checkfk;

%checkfk(pkTab=Sex, pk=Names.sex_id, fkTab=Names, fk=Sex.sex_id, out=results, print=yes);

Here is the log:

293 libname test "C:\Temp";
NOTE: Libref TEST was successfully assigned as follows:
Engine: V9
Physical Name: C:\Temp
294
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));
300 quit;
301 %mend checkfk;
302
303
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;
----
79
76
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

Re: select into doesn't work

deleted_user — Fri, 16 Nov 2007 23:51:00 GMT

I don't really follow what is going on here.

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.

Kind regards

David

Re: select into doesn't work

Cynthia_sas — Sat, 17 Nov 2007 02:05:06 GMT

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.

Your best bet for help with Macro questions is to read the SAS Macro facility documentation -- and not just to read it searching for one idea, but to really study it and run some of the examples and try to understand what's happening with the macro code. Or to search on Google or support.sas.com for papers or samples that are relevant or to contact Tech Support. Some relevant papers from a quick Google search are:
http://www2.sas.com/proceedings/sugi27/p071-27.pdf
http://www.ats.ucla.edu/stat/sas/library/nesug98/p193.pdf
http://www.ats.ucla.edu/STAT/sas/library/nesug99/cc107.pdf

You really will NOT be sorry if you start with a WORKING program first.

cynthia

Re: select into doesn't work

deleted_user — Mon, 19 Nov 2007 12:51:31 GMT

Sorry gyes, but I still cannot understand why it doesn't work:

%let numrows=0;
proc sql;
select count(name_id) into :numrows from test.Names
where test.Sex.sex_id = test.Names.sex_id;
quit;

It says that syntax error.

Re: select into doesn't work

advoss — Mon, 19 Nov 2007 14:43:06 GMT

Your query is referring to columns from two different tables, but your from statement only defines one table...

I believe it should be something like:
%let numrows=0;
proc sql;
select count(name_id) into :numrows
from test.Names,test.Sex
where test.Sex.sex_id = test.Names.sex_id;
quit;

Re: select into doesn't work

Cynthia_sas — Mon, 19 Nov 2007 14:53:35 GMT

Hi:
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:
[pre]
from test.Names as a, test.Sex as b
where a.sex_id = b.sex_id
[/pre]

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.

cynthia

Re: select into doesn't work

deleted_user — Mon, 19 Nov 2007 14:58:36 GMT

Yes, thanks, but it still doesn't work. Syntax error again.

Re: select into doesn't work

Cynthia_sas — Mon, 19 Nov 2007 15:14:25 GMT

Ihor:
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".

cynthia

Re: select into doesn't work

advoss — Mon, 19 Nov 2007 15:15:23 GMT

Yes, Cynthia is correct (as usual). You need to provide aliases rather than work with three part names.

Re: select into doesn't work

deleted_user — Tue, 20 Nov 2007 14:31:11 GMT

Thanks to all. Now it works. I just was not careful.