Check record count and then create table

[David_Billa]

I have the macro program as below. Now I want to write a code between execute statement and create table Libname.&prompt_tble_name to check the number of observation in &schema_temp..&prompt_tbl_name and if it's greater than 0 then only execute next step otherwise just put a note in the log. I was asked not to create separate macro for this.

%macro test;
/* macro variable definitions code will be here and then below code*/
execute(
        create or replace table &schema_temp..&prompt_tbl_name as
          select distinct &current_distinct_var_list
          from &schema_temp..&source_tbl
        ;
      ) by database
            
      create table Libname.&prompt_tble_name as
        select * from connection to database(
          select *
          from &schema_temp..&prompt_tbl_name;
        )
      ;
%mend;
%test;

I can test the number of observations using below method but I'm not certain how to use the &totobs to execute the next step which is  'create table libname.&prompt_tble_name'

data _NULL_;
 if 0 then set &schema_temp..&prompt_tbl_name nobs=n;
 call symputx('totobs',n);
 stop;
run;
%put no. of observations = &totobs;

[Reeza]

Example 13: Print Information to the Output Window If a Data Set Is Empty

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/mcrolref/p011imau3tm4jen1us2a45cyenz9.htm

Some common macro usages:
https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...

[yabwon]

Do a double check because some databases may not offer correct value of:

nobs=n

when you are using libname to connect.

Bart

[SASKiwi]

With external databases you would be better off checking row counts using the actual database, for example count(*). What database are you using?

[yabwon]

Try something like this:

%macro test(schema_temp,prompt_tbl_name);


execute(
        create or replace table &schema_temp..&prompt_tbl_name as
          select distinct &current_distinct_var_list
          from &schema_temp..&source_tbl
        ;
      ) by database

data _temp_;
  set &schema_temp..&prompt_tbl_name(obs=1);
run;
data _NULL_;
 call symputx('totobs',n);
 stop;
 set _TEMP_ nobs=n;
run;

%if &totobs > 0 %then %do;
      create table Libname.&prompt_tble_name as
        select * from connection to database(
          select *
          from &schema_temp..&prompt_tbl_name;
        )
      ;
%end;
%else
%do;
  %put NOTE: Zero obs!;
%end;


%mend;

%test(&schema_temp.,&prompt_tbl_name.)

Bart

[Patrick]

To use dataset option nobs you would need to load the data into SAS. If the purpose is to only load the data into SAS if the table has more than zero observations then you need to count them on the database side. 

Code like below should work. 

I can't think of any reason why you shouldn't use multiple Proc SQLs. In doing so the code would become less convoluted in my opinion and you have also more certainty that timing between SAS, SAS Macro and the database is as you need it.

%macro test();
  %let n_obs=0;
  select n_obs into :n_obs trimmed 
  from connection to database
  ( 
    select count(*) as n_obs from &schema_temp..&prompt_tbl_name 
  );
  %if &n_obs> 0 %then
    %do;

    %end;
%mend;

%test();

[Tom]

There is a disconnect here.  Your first step is running in some remote database.  Your example of how to check the number of observations is running in SAS. Are you sure they are checking the same table/dataset?

Are you saying that the macro variable SCHEMA_TEMP has both the name of the schema/database in the remote database and the name of a SAS libref that is pointing to the same schema/database?  (Note that would imply that the name of the schema in the remote database contains only letters, digits and underscores, does not start with a digit and is 8 characters or less long.) And if so are you sure that SAS can populate the NOBS= option of the SET statement for a table pulled from that remote database without actually pulling all of the data first?

If might be easier to test it explicitly.  So the logic flow would be something like:

connect .... ;

execute(
  create or replace table &schema_temp..&prompt_tbl_name as
    select distinct &current_distinct_var_list
    from &schema_temp..&source_tbl
) by database
;

%let nobs=0;
select nobs format=32. into :nobs trimmed
  from connection to database (
    select count(*) as nobs from &schema_temp..&prompt_tbl_name 
  )
;

%if &nobs %then %do;
create table Libname.&prompt_tble_name as
  select * from connection to database(
    select *
    from &schema_temp..&prompt_tbl_name;
  )
;
%end;

I do not see any need to create a new macro to introduce such simple logic. First if you already have the code in a macro you just need to modify it to add the new test.  And if you are not using a macro now there is no need to create a NEW macro just to use a %IF/%THEN/%DO/%END block as you can now run that in open SAS code.

[David_Billa]

If I want to identify the number of observations from SAS datasets instead of database table then do we have any other method other than select count(*) to fit here?

When  I tried the below method, I observed count(*)  step is executing for longer time. 

connect .... ;

create table WORK.t_&prompt_tbl_name as
        select * from connection to database(
          select distinct &current_distinct_var_list
          from &schema_temp..&source_tbl
        )
      ;

%let nobs=0;
select count(*)  format=32. into :nobs trimmed 
from WORK.t_&prompt_tbl_name
;

%if &nobs %then %do;
create table Libname.&prompt_tbl_name as
  select * from connection to database(
    select *
    from &schema_temp..&prompt_tbl_name;
  )
;
%end;

[Tom · Accepted solution]

If you just had PROC SQL create a SAS dataset then it already knows how many observations it wrote and placed that value into the automatic macro variable SQLOBS.  So just use that macro variable to test.  If you also want to trap when the create step failed for some reason and so SQL OBS did not get updated set it to zero before the create step.

%let sqlobs=0;
create table WORK.t_&prompt_tbl_name as
        select * from connection to database(
          select distinct &current_distinct_var_list
          from &schema_temp..&source_tbl
        )
      ;

%if &sqlobs %then %do;
...

If you want to test another SAS dataset that you have not just created with an PROC SQL SELECT statement then you can use the NOBS= option of the SET statement to get the count without actually reading the whole table.

data _null_;
  call symputx('nobs',nobs);
  stop;
  set WORK.t_&prompt_tbl_name(drop=_all_) nobs=nobs;
run;  

NOTE: The DROP= dataset option will make sure there is no name conflict with variable used to store the NOBS= results.