topic Re: is there a way to get note of proc sql after proc sql runs in SAS Programming

is there a way to get note of proc sql after proc sql runs

kwu — Wed, 27 Apr 2011 20:22:54 GMT

when we run proc sql like following:

proc sql noprint;
create table temp as select * from dataset1 where ... ;
quit;

the note will tell me TABLE temp created with x rows and y columns.

how can I get the x and y with out query the temp itself but from some variable that proc sql returns ?

Re: is there a way to get note of proc sql after proc sql runs

SPR — Wed, 27 Apr 2011 20:44:35 GMT

Hello Kwu,

I do not know any variable that proc SQL produces but this is an alternative way of doing this:
[pre]
proc sql;
create view t as
select name,age
from SASHELP.CLASS
;quit;
proc SQL noprint;
select COUNT(*) as x into :x
from t
;quit;
proc SQL noprint;
select COUNT(name) as y into :y
from SASHELP.VCOLUMN
where libname="WORK" and memname="T"
;quit;
%put rows=&x columns=&y;
[/pre]
where x,y are in macro variables with the same names.

Sincerely,
SPR

Re: is there a way to get note of proc sql after proc sql runs

polingjw — Wed, 27 Apr 2011 20:54:47 GMT

Here is a way to do it with a data step.

[pre]
data _null_;
if 0 then set dataset1 nobs=n;
array char{*} _character_;
array num{*} _numeric_;
vars = dim(char) + dim(num);
put "Note: The dataset has " n "observations and " vars "variables.";
stop;
run;
[/pre]

Re: is there a way to get note of proc sql after proc sql runs

DBailey — Wed, 27 Apr 2011 21:25:46 GMT

You can use the SQLOBS automatic variable.

SQLOBS
contains the number of rows that were processed by an SQL procedure statement. For example, the SQLOBS macro variable contains the number of rows that were formatted and displayed in SAS output by a SELECT statement or the number of rows that were deleted by a DELETE statement.

When the NOPRINT option is specified, the value of the SQLOBS macro variable depends on whether an output table, single macro variable, macro variable list, or macro variable range is created:

If no output table, macro variable list, or macro variable range is created, then SQLOBS contains the value 1.
If an output table is created, then SQLOBS contains the number of rows in the output table.
If a single macro variable is created, then SQLOBS contains the value 1.
If a macro variable list or macro variable range is created, then SQLOBS contains the number of rows that are processed to create the macro variable list or range.
If an SQL view is created, then SQLOBS contains the value 0.

Note: The SQLOBS automatic macro variable is assigned a value after the SQL SELECT statement executes.

Re: is there a way to get note of proc sql after proc sql runs

Peter_C — Thu, 28 Apr 2011 11:06:36 GMT

why not just run proc contents on your table, like
proc contents data= your_table out= work.conts varnum;
run;
You get a report of the details about each column as well as details about the table including number of rows and columns/variables and observations. You also get all that metadata as table WORK.CONTS
Less detail is provided by
proc sql noprint ;
select nobs, nvar into :nobs, :nvar
from dictionary.tables
where libname="%upcase(your_lib)"
and memname="%upcase(your_data_memname)"
;
%put your_data has &nobs rows and &nvar columns ;
quit ;
Notice that this sql queries dictionary.tables rather than your_lib.your_data_memname table.
Of course there is no alternative to running the query if you want the shape (nobs and nvar) of the output table, but you an still use the nobs/nvar query above for the output table
(assuming that output table is named work.temp)
proc sql noprint ;
select nobs, nvar into :nobs, :nvar
from dictionary.tables
where libname="WORK"
and memname="TEMP"
;
%put work.temp has &nobs rows and &nvar columns ;
quit ;

Re: is there a way to get note of proc sql after proc sql runs

Patrick — Thu, 28 Apr 2011 13:07:34 GMT

Hi
As already suggested you could use automatically created macro variables, i.e:

proc sql;
create table test as
select *
from sashelp.class
;
%put SQLRC : &sqlrc;
%put sqlobs: &sqlobs;
quit;

You can find documentation at the end of:
http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/a001360983.htm

HTH
Patrick