MVAR use in proc Template

[Olivier]

Hi all.
I was wondering why the following test did not work : I've tried to change the template for the SQL procedure so that a header includes both editing date and number of rows listed. For the date, I declared the system macro-variable SYSDATE9 in a MVAR statement in proc Template ; for the number of rows, I used the SQLOBS system macro-variable in the same way.
SYSDATE9 works out fine ; SQLOBS does not (it resolves to zero in the header ; but a %PUT shows that it resolves to 19).
I guess that it has to do with a synchronization problem between the template and the Sql procedure. But I am not really clear about that : I thought that the query result was already built up before being sent to ODS, and hence the SQLOBS should have been given a value at that time !

Does someone have a clearer view on what's happening than mine ? Thanks in advance.

Olivier

PROC TEMPLATE ;
EDIT Base.Sql ;
DEFINE HEADER h ;
MVAR sqlobs sysdate9 ;
TEXT "This output composed of " sqlobs " observations was produced on " sysdate9 "..." ;
END ;
END ;
RUN ;
PROC SQL ;
SELECT *
FROM sashelp.class ;
%PUT &sqlobs ;
QUIT ;

[Cynthia_sas]

Olivier:
I think you may be running into this. Even though the doc is referring to data step created macro vars, this is what it says:
"You cannot use a macro variable reference to retrieve the value of a macro variable in the same program (or step) in which SYMPUT creates that macro variable and assigns it a value."

Or, if you prefer to think of it a separate way...think of the header being written at the "top" of the file. So, at the time the header needs to be written, no step boundary has yet occurred. A different way to accomplish the same thing is:
[pre]
PROC TEMPLATE ;
EDIT Base.Sql ;
MVAR nobs sysdate9 ;
DEFINE HEADER h ;
TEXT "This output composed of " nobs " observations was produced on " sysdate9 "..." ;
END ;
END ;
RUN ;

ods listing;
PROC SQL ;
select nobs into :nobs
from dictionary.tables
where libname = 'SASHELP' and
memname = 'CLASS';

%put nobs is &nobs;
%let nobs=&nobs; /* <------- this strips trailing blanks */

SELECT *
FROM sashelp.class ;
QUIT ;
[/pre]

I remember seeing a SUGI paper a while back, that showed how to get nobs using just %sysfunc and SCL functions to open the dataset, get nobs and then close the data set which might be another alternative.

cynthia

[deleted_user]

There certainly is Cynthia. I wrote this macro into my toolkit in 2000, so I haven't looked at any changes of behaviour in the move from V6 to SAS8 and later.

%Macro XGetDNob( MDSName = , /* NObs in this data set */
MMacVar = ) /* Place NObs in this macro */
/ /*Store*/ Des = 'Get data set observation count to macro';


%Local DSid;

%Global &MMacVar;

%Let &MMacVar = -1; /* If data set does not exist,
force -1 return value */

%Let DSid = %SysFunc( Open( &MDSName) );

%If &DSid %Then %Do;

%Let &MMacVar = %SysFunc( Attrn( &DSid, NLObs) );

%Let SysRc = %SysFunc( Close( &DSid) );
%If &SysRc %Then
%Put Macro Call Error: Data Set &MDSName was not closed properly;

%End;

%Else %Put Macro Call error: - %SysFunc( SysMsg( ) );


%Mend XGetDNob;


There was an extended version I also wrote that could retrieve any data set attribute. With the extended V9 data set attributes, this has proven very useful as well.

I know you recognise the meaning of the attribute NLObs, but it is worth documenting that it is the number of Logical Observations, and will not include obs marked for deletion, which are otherwise included in the NObs count. It is a reminder that when you look under the bonnet in SAS, you need to know what you are looking for.

Kind regards

David

[Olivier]

Thank you Cynthia and David.
If I understand well, the macro-variable SQLOBS is set to zero prior to the SQL query is beign run ; that's when the Template gets its value. And then the query is run, and the correct number of rows is assigned to the SQLOBS macro-variable, but it's already too late !
Well, thanks anyway for both the explanations and the circumventions.

Regards,
Olivier