02-19-2018 02:35 AM - edited 02-19-2018 02:44 AM
I want to store count of dataset in variable like below
%let Cnt ;
create table work.delaycheck as
select * from connection to oracle
I want to store count of this table in the variable Cnt like below
Set Cnt = (Select count(*) from work.delaycheck )
Insert into Oracle_table
select * from work.delaycheck
) by oracle;
disconnect from oracle;
How can I acheive these steps? Thanks In advance!!
02-19-2018 04:14 AM
Why? Every dataset within the SAS system already has metadata associated with the dataset stored. You can access this metadata directly by using the sashelp.vtable (and if you want columns, sashelp.vcolumn) or via the SQL syntax of dictionary.tables. Therefore you a) do not need to code this yourself, and b) do not need to store the numeric in a text macro variable. So for instance:
set cnt=(select nobs from sashelp.vtable where libname="WORK" and memname="ABC");
Assuming your dataset is work.abc.
02-21-2018 07:49 AM
Why do you want to make the insert conditional? When the table is empty, inserting in into another table does exactly nothing, and takes very little time.
But if you need to know this for other reasons, PROC SQL assigns the macro variable SQLOBS, which contains the number of rows in the output from the last SQL statement. So you could use that like this:
%macro insert; proc SQL; /* code to create work table here */ %if &sqlobs>0 %then %do; /* code to do something with table here */ %end; quit; %mend; %insert;
Of course, you cannot use a SQL EXECUTE call to insert rows from a SAS table. You would either have to load the data to a (temporary) Oracle table, and the perform the insert. Alternatively, you can assign the Oracle schema as a SAS libname and use e.g. PROC APPEND to insert your data:
proc SQL; connect to Oracle(<connnect options>); create table work.delaycheck as select * from connection to Oracle (<Oracle select code>); quit; libname outlib Oracle <connect options>; proc append data=delaycheck base=outlib.Oracle_table; run;