topic Re: How to assign a Count of dataset to variable and use it in IF ELSE statement in SAS Procedures

How to assign a Count of dataset to variable and use it in IF ELSE statement

Pradeepbanu — Mon, 19 Feb 2018 07:44:53 GMT

I want to store count of dataset in variable like below

%let Cnt ;

create table work.delaycheck as
select * from connection to oracle
(

SELECT PTNR_ID,CLNT_ID,REPORTING_DATE_KEY,NET_SALES
FROM FACT_TABLE

MINUS

SELECT PTNR_ID,CLNT_ID,REPORTING_DATE_KEY,NET_SALES
FROM HIST_FCT

);

I want to store count of this table in the variable Cnt like below

Set Cnt = (Select count(*) from work.delaycheck )

And Then

If(Cnt=0)

THEN

DO NOTHING

ELSE

execute(

Insert into Oracle_table

select * from work.delaycheck

) by oracle;

disconnect from oracle;

quit;

How can I acheive these steps? Thanks In advance!!

Re: How to assign a Count of dataset to variable and use it in IF ELSE statement

RW9 — Mon, 19 Feb 2018 09:14:23 GMT

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.

Re: How to assign a Count of dataset to variable and use it in IF ELSE statement

s_lassen — Wed, 21 Feb 2018 12:49:11 GMT

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;