topic Re: Check sqlobs in each dataset and then create/drop dataset in SAS Programming

Check sqlobs in each dataset and then create/drop dataset

David_Billa — Tue, 28 Feb 2023 17:15:47 GMT

I've the below macro code which create SAS dataset from database table and then I'm checking the record count and if it has record then it will drop and rename the SAS dataset. Instead of doing this record check and drop/rename the table for every iteration, I want to check the number of observations for each table and only if we there is record for all the tables then only drop/rename the table. Total number of SAS table creation is depends on the number of values in the macro variable 'type_lt'.

So after this line '%if &sqlobs %then %do;' I would like to know if we can create one more macro variable to add the counter when we have observation.of each APP.t_&pmt_tbl_name and when we have observations all the tables then I have to execute drop/rename step. I'm not certain if the proc datasets step move outside of the loop.

Assume we have four iterations for this macro then we have to drop/rename four datasets if we have obervation in APP.t_&pmt_tbl_name all the iterations. Any help?

% macro test;
proc sql;
%do type_id=1 %to %sysfunc(countw(&type_lt,,s));
........
%let sqlobs=0;	  
	 
create table APP.t_&pmt_tbl_name as
select * from connection to database(
select distinct &current_distinct_var_list
from &schema_temp..&source_tbl
        )
      ;
     
%if &sqlobs %then %do;
     
proc datasets lib=APP noprint;
delete &pmt_tbl_name;
change t_&pmt_tbl_name=&pmt_tbl_name;
quit;        
      ;
%end;
quit;
;
%end;
.........
%mend;

%let type_lt = 
  a*b*c
  d*e
  e*f*g*h
  a*g*i
;

%test(
  type_lt = &type_lt
)

Re: Check sqlobs in each dataset and then create/drop dataset

Tom — Tue, 28 Feb 2023 17:27:13 GMT

It is not clear what code you want to generate, but you can just save the SQLOBS results from the creation step into you own macro variables and then use that later if you want. In general it is good practice to save the SQLOBS result since its value will be overwritten by the results from the next SQL SELECT statement you run.

For example to just generate macro variables named COUNT1, COUNT2, etc. you could add this %LET statement:

%let count&type_id = &sqlobs;

Re: Check sqlobs in each dataset and then create/drop dataset

David_Billa — Tue, 28 Feb 2023 17:45:04 GMT

@Tom In the below step we are checking the value of sqlobs and then doing the delete/rename for each iteration.

%if &sqlobs %then %do;
     
proc datasets lib=APP noprint;
delete &pmt_tbl_name;
change t_&pmt_tbl_name=&pmt_tbl_name;
quit;        
      ;

Now I want to break that approach into parts. a) Check the value of sqlobs of each iteration. If it's greater than 0 then create one macro varible like % let counter=1.

If there are three iterations in the macro and if we have observations in the each of the dataset then value of counter macro varible should increase to 3. Now number of iterations is same as value of counter macro variable.

b) if the of number of iterations is equal to value of counter macro variable then execute the below step.

proc datasets lib=APP noprint;
delete &pmt_tbl_name;
change t_&pmt_tbl_name=&pmt_tbl_name;
quit;

Re: Check sqlobs in each dataset and then create/drop dataset

Tom — Tue, 28 Feb 2023 17:56:41 GMT

You can use %EVAL() to do simple integer arithmetic. SAS will evaluate boolean expression to 1 for TRUE and 0 for FALSE. So to increment when SQLOBS is not zero you could use:

%let counter=%eval(&counter + (&sqlobs>0));

But if your logic gets more complex you will probably want to abandon pure macro code and instead put the data into a dataset where you can use the full power of the actual SAS language instead of the minimal computational power of the macro language.

data count;
  stop;
  length dsname $100 count 8;
run;
proc sql;
....
insert into count(dsname,count) values ("&schema..&table",&sqlobs);
...

Re: Check sqlobs in each dataset and then create/drop dataset

David_Billa — Tue, 28 Feb 2023 18:29:38 GMT

@Tom I think I'm missing something from your idea. I tried the following code by adding counter macro variable and it is errored. I don't see any delete and rename dataset step in your code.

Assume if we have three iterations and the value of 'counter' macro variable is also then i want to delete the already existing tables and rename the tables. This step also should execute three times after the value of macro variable is resolved to 3.

%if &nobs %then %do;
%let counter=%eval(&counter + (&sqlobs>0));
%put ####Counter=&counter.;
      
      create table &prompt_table_name as
        select * from connection to database(
          select *
          from &schema_temp..&prompt_tbl_name;
        )
      ;
%end;

Error:

SYMBOLGEN:  Macro variable NOBS resolves to 887
MLOGIC(TEST):  %IF condition &nobs is TRUE
MLOGIC(TEST):  %LET (variable name is COUNTER)
WARNING: Apparent symbolic reference COUNTER not resolved.
SYMBOLGEN:  Macro variable SQLOBS resolves to 1
WARNING: Apparent symbolic reference COUNTER not resolved.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: 
       &counter + (1>0) 
ERROR: The macro TEST will stop executing.
MLOGIC(TEST):  Ending execution.