Dynamically get the dataset name and count of datasets and update the table

[David_Billa]

I'm trying to dynamically get the table name and table count from macro and want to update it in another table , but I'm unable to get it done.

Given below the code which I tried. Here 'tab' variable will have the table names and it is dynmaic. It may have one table name upto many but it will not be missing.

Assume if 'tab' has two table names, then for each table name, I'm trying to execute the macro

STATUS_TECH_UPDATE to get the table name and table count and update the table test.status_tech  but it is not executing as I excepted.

In simple terms, if the macro variables has two dataset names, then I need two records in test.status_tech with table names and count of records for each table.

Assume if the 'tab' variable has values  'Account_Balance' and 'Customer' then I want to insert two records into test.status_tech table as follows. 'tab' can hold any number of values and it is dynamic.

test.status_tech:

table_name	table_count
Account_Balance	7
Customer	8

Appericiate if someone of you help me resolve the issue. I'm OK with any other apporach as well.

options symbolgen mlogic mprint;

/*Get the RUN_ID*/
proc sql;
select "'"||compress(RUN_ID)||"'" into :mac separated by ","
from test.status_tech;
quit;

/*Macro to run for each dataset to get the table_name and table_count */

%MACRO STATUS_TECH_UPDATE(tab);                                                                                                                                       
   %put &tab; 
   %put "inside macro";
     proc sql;
	create table &tab AS
    select count(*) AS COUNT, B.RUN_ID from test.&tab A, STATUS_TECH B
	where A.RUN_ID = B.RUN_ID;
    quit;

    proc sql;
    update test.status_tech(where = (RUN_ID IN (&mac.))) as s_m
	set table_name  =&tab.;
	table_count =(select count from &tab.);
	quit;
     
%MEND;
    
/*pass value to the macro*/
/*tab variable holds the table name*/
data LD_TABLES_1(keep=table);
set FileList_1;
call symput("LD_TABLES",table);
tab=SYMGET('LD_TABLES'); /*LD_TABLES resolves to table names*/
call execute('%STATUS_TECH_UPDATE('||tab||'); ');
run;

MLOGIC(STATUS_TECH_UPDATE):  Beginning execution.
MLOGIC(STATUS_TECH_UPDATE):  Parameter TAB has value ACCOUNT_BALANCE
MLOGIC(STATUS_TECH_UPDATE):  %PUT &tab
SYMBOLGEN:  Macro variable TAB resolves to ACCOUNT_BALANCE
GL_ACCOUNT_BALANCE_SEGMENT
MLOGIC(STATUS_TECH_UPDATE):  %PUT "inside macro"
"inside macro"
MPRINT(STATUS_TECH_UPDATE):   proc sql;
SYMBOLGEN:  Macro variable TAB resolves to ACCOUNT_BALANCE
MPRINT(STATUS_TECH_UPDATE):   create table ACCOUNT_BALANCE AS select count(*) AS COUNT, B.RUN_ID from 
test.ACCOUNT_BALANCE A, STATUS_TECH B where A.RUN_ID = B.RUN_ID;
MPRINT(STATUS_TECH_UPDATE):   quit;
MPRINT(STATUS_TECH_UPDATE):   proc sql;
SYMBOLGEN:  Macro variable TAB resolves to ACCOUNT_BALANCE
MPRINT(STATUS_TECH_UPDATE):   update test.status_tech set table_name =ACCOUNT_BALANCE;
MPRINT(STATUS_TECH_UPDATE):   table_count =(select count from  &tab.);
MPRINT(STATUS_TECH_UPDATE):   quit;
MLOGIC(STATUS_TECH_UPDATE):  Ending execution.

1         +
          proc sql;     update test.status_tech  set table_name

2         + =ACCOUNT_BALANCE;
ERROR: The following columns were not found in the contributing tables: ACCOUNT_BALANCE.
ERROR: The following columns were not found in the contributing tables: ACCOUNT_BALANCE
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
2         +                               *table_count =(select count from tab.);  quit;
NOTE: The SAS System stopped processing this step because of errors.

[yabwon]

Could you share some example data?

Bart

[David_Billa]

Assume if the 'tab' variable resolves to 'Account_Balance' and 'Customer' then I need the output as follows.

table_name	table_count
Account_Balance	7
Customer	8

[yabwon]

You want us to help you, but give us example data which aren't data... Don't be lazy.

What are: status_tech table and run_id variable?

All the best

Bart

[Tom]

Please explain in detail what you are trying to do, and if possible give the larger context.

The sense I get from looking at the code is that you want to count the number of observations in some table based on the value of some variable.  Isn't that what PROC FREQ is for?

[David_Billa]

@Tom @yabwon 

You can forget about the run_id variable which I used in my code. I want to insert records into test.status_tech based on the values from the variable 'tab'. If It has one value, then I want to insert one record into test.status_tech table and if it has two values then I need to insert  two records.

Assume if the 'tab' variable has values  'Account_Balance' and 'Customer' then I want to insert two records into test.status_tech table as follows. 'tab' can hold any number of values and it is dynamic. 

test.status_tech is a empty table and we have to feed the record with table_count and table_name values as shown below.

If you got my point, you can help me with your own approach or correct my approach or  you can point me to any document which does this.

test.status_tech:

table_name	table_count
Account_Balance	7
Customer	8

[yabwon]

Do you need something like the following code does:

options dlcreatedir;
libname test "%sysfunc(pathname(work))/test";

data test.Account_Balance;
  do i = 1 to 7;
    output;
  end;
run;

data test.Customer;
  do j = 1 to 8;
    output;
  end;
run;

data test.status_tech;
  length table_name $ 41;
  table_name = "Account_Balance"; table_count = .; output;
  table_name = "Customer"; table_count = .; output;
run;

data FileList_1;
  table = "Account_Balance"; output;
  table = "Customer"; output;
run;


options symbolgen mlogic mprint;


%MACRO STATUS_TECH_UPDATE(tab);                                                                                                                                       
   %put &tab; 
   %put "inside macro";
   data _tmp_;
    keep table_count table_name;
    length table_name $ 41;
    table_count = nobs; 
    table_name = "&tab";
    output;
    stop;
    set test.&tab nobs=nobs;
   run;
   proc append base = work.FOR_UPDATE data = _tmp_;
    run;
   proc delete data = _tmp_;
    run;
%MEND;

title "Before";
proc print data = test.status_tech;
run;


/* first iteration */ 
data LD_TABLES_1(keep=table);
  set FileList_1;
  call execute('%nrstr(%STATUS_TECH_UPDATE('||table||')); ');
run;

proc sort data = work.FOR_UPDATE;
  by table_name;
run;
proc sort data = test.status_tech;
  by table_name;
run;

data test.status_tech;
  update test.status_tech work.FOR_UPDATE;
  by table_name;
run;
proc delete data = work.FOR_UPDATE;
run;


title "After first execution";
proc print data = test.status_tech;
run;


/* new records added */
data test.Account_Balance;
  do i = 1 to 17;
    output;
  end;
run;

data test.Customer;
  do j = 1 to 18;
    output;
  end;
run;

/* second iteration */
data LD_TABLES_1(keep=table);
  set FileList_1;
  call execute('%nrstr(%STATUS_TECH_UPDATE('||table||')); ');
run;

proc sort data = work.FOR_UPDATE;
  by table_name;
run;
proc sort data = test.status_tech;
  by table_name;
run;

data test.status_tech;
  update test.status_tech work.FOR_UPDATE;
  by table_name;
run;
proc delete data = work.FOR_UPDATE;
run;

title "After second execution";
proc print data = test.status_tech;
run;

All the best

Bart

[David_Billa]

Thanks for your code but it's not correct.

How do you tackle if you don't know the number of observations in
ACCOUNT_BALANCE and CUSTOMER dataset?

[yabwon · Accepted solution]

Hi,

if the `test` libname is a SAS library then NOBS= is always available, in case test is link to external database you will need regular `select count(1) from &tab.;`

%MACRO STATUS_TECH_UPDATE(tab);                                                                                                                                       
   %put &tab; 
   %put "inside macro";
   
   proc sql;
    create table _tmp_ as
    select 
      "&tab" as table_name length 41
     ,count(1) as table_count
    from test.&tab;
   quit;

   proc append base = work.FOR_UPDATE data = _tmp_;
    run;
   proc delete data = _tmp_;
    run;
%MEND;

All the best

Bart

[David_Billa]

Thanks. Instead of proc append, can we use proc sql update?

[yabwon]

Hi,

try with this:

%MACRO STATUS_TECH_UPDATE(tab);                                                                                                                                       
   %put &tab; 
   %put "inside macro";
   
   proc sql;
   %if %sysfunc(exist(work.FOR_UPDATE)) %then
   %do;
    insert into work.FOR_UPDATE
   %end;
   %else
   %do;
    create table work.FOR_UPDATE as
   %end;
    select 
      "&tab" as table_name length 41
     ,count(1) as table_count
    from test.&tab;
   quit;
%MEND;

All the best

Bart

[Tom]

That helps a little. But you have not described your input.  What dataset is the variable TAB in?  Is it always the same dataset?  What is it you are counting? 

To me it sounds like there is some unnamed dataset, lets call it HAVE, that has a variable named TAB. You want to count the number of observations per value of TAB and insert those counts into a dataset named TEST.STATUS_TECH.

So let's first setup up some actual test data.  We will use WORK datasets since we don't have access to your TEST library.

data status_tech;
  length table_name $32 table_count 8;
  input table_name table_count;
cards;
Account_Balance 7
Customer 8
;
data have;
  length tab $32 ;
  input tab @@;
cards;
fred fred sam sam fred sam sam sam 
;

Now let's count values of TAB and insert into STATUS_TECH.

You could use SQL.

proc sql;
  insert into status_tech (table_name,table_count)
   select tab,count(*) from have group by tab
 ;
quit;

Results:

                          table_
Obs    table_name          count

 1     Account_Balance       7
 2     Customer              8
 3     fred                  3
 4     sam                   5

If I didn't translate the problem right then please correct. Using your own example input and output datasets. Make sure to post them as code using the Insert SAS Code button in the forum editor.

[David_Billa]

I would like to correct your understanding.

In your example, fred and sam is a values to the variables 'tab'. Now
assume fred and sam is some SAS datasets name ,then you have to count the
observations from the datasets 'Fred' and 'Sam' and write it to
test.status_tech.

E.g. test.status_tech should looks like

Table_name Table_count
Fred 12
Sam 44

[Tom]

---

@David_Billa wrote:
I would like to correct your understanding.

In your example, fred and sam is a values to the variables 'tab'. Now
assume fred and sam is some SAS datasets name ,then you have to count the
observations from the datasets 'Fred' and 'Sam' and write it to
test.status_tech.

E.g. test.status_tech should looks like

Table_name Table_count
Fred 12
Sam 44

---

In that case you need to GENERATE code from the data. 

You can use CALL EXECUTE().

data _null_;
  set have end=eof;
  if _n_=1 then call execute('proc sql');
  if exist(tab) then call execute(catx(' '
    ,'insert into status_tech (table_name,table_count)'
    ,'select',quote(trim(tab)),',count(*) from ',tab,';'
  ));
  if eof then call execute('quit;');
run;

[David_Billa]

Thanks. Any other ways apart from call execute?