Odd Behavior - SAS hangs

[deleted_user]

I wrote earlier today regarding an issue with an invalid statements in a 'proc sql'.
I tried to work around it using a macro - dataview.
The code below works but the Log output is blank and SAS hangs. The original proc is at the very bottom below.
Another question - Is there a way to format code so that is doesn't slam against the left margin?
..
..
%let dataview = 'TIMD.v_Feed_Results_INT'; ** Internal/External data view
%let test = 43271; ** change test number
%let num_anml = 25; ** change number of animals

other code steps .....
.....
....
....
..... New Step - runs to completion and correct results are in the database,
..... but Log Output is blank
..... and SAS hangs - have to use Task Manager to "kill" it.
proc sql;
create view datavalv as
select Data_Validity_Code,
SAK_Feeding_Test_Res
from &dataview
where test_num = &test;
quit;

..... Original step
proc sql;
create view datavalv as
select Data_Validity_Code,
SAK_Feeding_Test_Res
from TIMD.v_Feed_Results_INT
where test_num = &test;
quit;

[Cynthia_sas]

Hi:
I see a few issues in your code:
1) no semicolon after the comment (which I assumed was a copying typo when you posted it in your previous entry):
Incorrect: [pre]
%let dataview = 'TIMD.v_Feed_Results_INT'; ** Internal/External data view
%let test = 43271; ** change test number
%let num_anml = 25; ** change number of animals
[/pre]

Correct:[pre]
%let dataview = 'TIMD.v_Feed_Results_INT'; ** Internal/External data view;
%let test = 43271; ** change test number;
%let num_anml = 25; ** change number of animals;

OR
%let dataview = 'TIMD.v_Feed_Results_INT'; /* Internal/External data view*/
%let test = 43271; /* change test number */
%let num_anml = 25; /* change number of animals*/
[/pre]

A SAS comment starts with an asterisk and ends with a semi-colon OR is anything enclosed in /* ... */ which also can be used to provide comments.
If you really did have the code as you showed it, without a semicolon, then you would effectively be "ruining" the subsequent statements. the final comment, "**change number of animals" would be unclosed and so whatever is in the other code steps after that comment could be messed up, which might lead to quoting problems or step boundary problems.

2) it is NOT appropriate to refer to a SAS dataset inside a quoted string. Generally, it's not a good idea to "prequote" macro variables anyway. You can prove this to yourself by submitting the following code:
[pre]
title '1) use sas name without quotes';
ods listing;
proc sql;
select *
from sashelp.class
quit;

title '2) use operating system filename and file extension';
ods listing;
proc sql;
select *
from 'c:\temp\class.sas7bdat';
quit;

title '3) use quotes around sas name';
ods listing;
proc sql;
select *
from "sashelp.class";
quit;

title '4) use macro variable reference correctly';
%let indata = sashelp.class;
ods listing;
proc sql;
select *
from &indata;
quit;
[/pre]

You will see output from steps 1, 2 and 4. #4 example shows the correct way to use a macro variable reference (unquoted) to provide a SAS data set name.

#3 example code (which is the equivalent of what your macro is doing) will get you this ERROR in the LOG:
[pre]
3991 title '3) use quotes around sas name';
3992 ods listing;
3993 proc sql;
3994 select *
3995 from "sashelp.class";
ERROR: Extension for physical file name "sashelp.class" does not correspond to a valid member type.
3996 quit;

[/pre]

I suspect that the reason you're not seeing anything in the SAS log and the reason that SAS is hanging is that your session is either lost in "quote-land" or "mismatched comment land" and the only thing to do at this point is SAVE your work, if you can, close SAS and start over again. Submit your code in smaller chunks and review the SAS LOG after EVERY little chunk until you hit the first error message or place where SAS stops working. Then look at the code immediately above that location and search for mismatched quotes, inappropriate macro variable references, mismatched opening and closing comments, missing semi-colons, etc etc.

If you continue to have issues, since it appears that you have lengthy code, which may or may not involve SAS macro programs, you might want to work with Tech Support, as they can look at ALL of your code and help you find the problem. One of the things about SAS error messages is that the place where an error message finally appears may not be related to the place where the error first occurred in your code. So something could have gone wrong in the place where you have "other code steps...".

cynthia

[deleted_user]

Here is first chunk of code (how do I get the pasted code to maintain indent):

%let dataview = 'TIMD.v_Feed_Results_INT'; ** Internal/External data view **;
%let test = 43271; ** change test number **;
%let num_anml = 25; ** change number of animals **;

The problem is in this chunk of code:

******************************************;
** access data validity information **;
******************************************;
proc sql;
create view datavalv as
select Data_Validity_Code,
SAK_Feeding_Test_Res
from &dataview
where test_num = &test;
quit;

If I change this one line from:

from &dataview

to

from TIMD.v_Feed_Results_INT

The code runs fine, the Output pane is populated and SAS does not hang.

Another oddity is that even though SAS hangs, it does perform the very last step which is a "proc dbload' that inserts the results to a table on Oracle.

[Patrick]

As Cynthia wrote already: Get rid of the quotes!

The way you do it your macro var &dataview resolves to the quoted string 'TIMD.v_Feed_Results_INT' and not to the unquoted string you're using: TIMD.v_Feed_Results_INT - you're therefore comparing apples with oranges.

HTH
Patrick

[deleted_user]

Rather that fight with SAS I decided to create a PL/SQL view in the database that handles the difference between internal and external tests.

Worked the first time without all the hassels of SAS code.

Thanks Cynthia & Patrick for you assistance.