Statement is not valid - Error 180-322

[deleted_user]

I inherited some SAS scripts written by a non-programmer. I am new to SAS and need to make a change. Here is a simple piece of code that 'should' choose a SQL statement based on the predefined variable - ttest:

proc sql;
if ttype = 'I' then
do;
create view datavalv as
select T.data_validity_code,
R.sak_feeding_test_res
from TIMD.feeding_test_results T
left join TIMD.feeding_test_remarks R on
(T.sak_feeding_test_res = R.sak_feeding_test_res)
where T.test_num = &test;
end;
else
do;
create view datavalv as
select data_validity_code,
sak_outside_feed_res as sak_feeding_test_res
from TIMD.outside_feeding_results
where outside_feeding_results.test_num = &test;
end;
quit;

here is a snippet of the error reported:

709 (T.sak_feeding_test_res = R.sak_feeding_test_res)
710 where T.test_num = &test;
NOTE: SQL view WORK.DATAVALV has been defined.
711 end;
---
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

712 else
----
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

713 do;

Should be simple, but I am having no success.

[Cynthia_sas]

Hi:
Two questions:
1) Was the code working before you inherited it? Did it ever successfully generate output? Or is this pseudo-code that conceptually shows the type of conditional processing that should take place?

2) Did you remove any % or & punctuation from the code or any %macro or %mend statements?

cynthia

[deleted_user]

Sorry, I was not clear. I added the if/else structure. I have to make this script generic so it will run internal or external test (they have different tables and columns).
Here is the original 'Proc sql':
proc sql;
create view datavalv as
select data_validity_code, feeding_test_remarks.sak_feeding_test_res
from tims.feeding_test_results left join tims.feeding_test_remarks
on feeding_test_results.sak_feeding_test_res = feeding_test_remarks.sak_feeding_test_res
where feeding_test_results.test_num = &test;
quit;

[Cynthia_sas]

Hi:
Where will TTYPE come from? Is it already in the data or is it a parameter that the person using the code will supply? How do you envision TTYPE being supplied? It looks to me as though the data sets being processed determine the type of SQL code.

cynthia

[deleted_user]

Here is the tetst specific code from the top of the script:

%let ttype = 'I'; ** Internal/External flag
%let test = 43271; ** change test number
%let num_anml = 25; ** change number of animals

[Cynthia_sas]

Hi:
First some terminology -- the SAS programming language is not a scripting language. I think of a script program (like JavaScript or PHP or Perl) as something that executes immediately and is not compiled.

SAS code is compiled, and then, if free of compile errors, is executed. In my mind, that makes SAS something OTHER than a scripting language like JavaScript. I'd call a group of SAS statements a program, not a script. A SAS program can be composed of 1 step or of multiple steps. However, each step is compiled before it is executed.

A SAS program is basically composed of DATA steps and PROCEDURE steps (most commonly called PROC steps). (along with option statements and other global statements that have an impact on how the program is executed or how the output is treated) A DATA step program allows you to use SAS language syntax, such as IF/THEN statements, assignment statements, ARRAYs, DO loops, etc.

A PROC step program allows you to use "canned" statements that have pre-defined ways of operating. So for example, PROC MEANS has a VAR statement; PROC SQL has a SELECT statment. Neither PROC MEANS nor PROC SQL (for example) allow an IF statement to be used inside their syntax. Regular SAS IF statements belong to a DATA step program. So you got an error because you used an IF statement in the wrong place.

However, another rule of the SAS programming language is that you can't mix DATA step syntax and PROC syntax...so for example, you CANNOT do this:
[pre]
DATA TEST;
if TTEST = 'T' then do;
proc sql;
......... more proc sql code .........
end;
run;
[/pre]

That's because DATA step programs and PROC step programs are "self-contained". So how do you define and generate generic reusable code??? Enter the SAS Macro facility.

There is an additional piece of SAS language called the SAS Macro Facility. The SAS Macro facility is used to perform text substitution and to help you generate valid code that will compile and then execute. Just like a DATA step program has an IF statement, you can use a %IF statement inside a MACRO program to generate program code conditionally.

What happens is that the Macro facility grabs any macro "triggers" like % or & and resolves them BEFORE any code is compiled. What the compiler sees is the resolved SAS code. The SAS Macro facility doesn't "run" anything -- it only takes a macro program or macro variable references and resolves the program into valid SAS statements or takes the macro variable references and resolves them in code (like a TITLE statement or other statements).

A good tutorial or place to start with the SAS Macro facility is here:
http://www2.sas.com/proceedings/sugi28/056-28.pdf

Of course the Macro documentation is good, too, and there's a lot to Google on SAS Global Forum papers about using SAS Macro programs to generate reusable, generic code. I suggest you start with the tutorial and if that doesn't help, then look for some other examples of using SAS Macro programs to conditionally generate program statements.

cynthia