CODEWITHSUNDEEP
sas
Tpk43
Tpk43

Reputation: 331

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

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

%put = (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!!

Upvotes: 0

Views: 448

Answers (2)

Richard
Richard

Reputation: 27516

All of the SQL and data shown is occurring remotely. You can perform all the activity there without involving SAS. Oracle will process 

PROC SQL;
  CONNECT TO ORACLE ...;
  EXECUTE (
    INSERT INTO <TARGET_TABLE>
    SELECT * FROM 
    ( 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
    )
 ) BY ORACLE;

and not insert any records if the fact table is comprised of only historical facts.

EXECUTE can also submit PL/SQL statements, which in-turn can reduce the need for extraneous system interplay.

Upvotes: 1

momo1644
momo1644

Reputation: 1804

  1. Delete this line from your code

    %let Cnt ;

  2. In order to get the count: Add the code below which will create the macro variable Cnt with the count:

    proc sql; Select count(*) into: Cnt from work.delaycheck ; quit;

  3. Update the if statement: the "&" is used to reference macro variables

    If &cnt=0

The Code below shows how to use the if/else and the use of Call Execute:

data _null_;
if &cnt=0 then put 'Cnt is 0';/*if true: a note is written to the log*/
else call execute ('proc print data=work.e; run;'); 
/*else clause: the Proc Print code is executed*/
run;

Upvotes: 1

Related Questions