topic Re: Dynamically get the dataset name and count of datasets and update the table in SAS Programming

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

David_Billa — Sat, 18 Apr 2020 16:09:08 GMT

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.

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

yabwon — Sat, 18 Apr 2020 15:04:03 GMT

Could you share some example data?

Bart

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

David_Billa — Sat, 18 Apr 2020 15:13:40 GMT

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

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

yabwon — Sat, 18 Apr 2020 15:21:27 GMT

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

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

Tom — Sat, 18 Apr 2020 15:36:34 GMT

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?

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

David_Billa — Sat, 18 Apr 2020 16:12:07 GMT

@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.

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

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

yabwon — Sat, 18 Apr 2020 16:21:41 GMT

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

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

David_Billa — Sat, 18 Apr 2020 16:31:36 GMT

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?

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

yabwon — Sat, 18 Apr 2020 17:24:38 GMT

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

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

David_Billa — Sat, 18 Apr 2020 17:57:36 GMT

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

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

Tom — Sat, 18 Apr 2020 18:06:01 GMT

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.

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

yabwon — Sat, 18 Apr 2020 18:08:12 GMT

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

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

David_Billa — Sat, 18 Apr 2020 18:24:36 GMT

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

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

Tom — Sat, 18 Apr 2020 19:31:09 GMT

@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;

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

David_Billa — Sun, 19 Apr 2020 00:04:36 GMT

Thanks. Any other ways apart from call execute?

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

Tom — Sun, 19 Apr 2020 00:20:27 GMT

Sure. Since you still haven't described what you have or what you are trying to achieve we can only give examples of patterns that you can try to adopt.

You can write code to a text file and use %INCLUDE to run it. That has the advantage of letting you use the power of the PUT statement and also for you to review the code before running it to debug the code generation logic.

filename code temp;
data _null_;
  set have end=eof;
  file code ;
  if exist(tab) then put
    'insert into status_tech (table_name,table_count) '
    'select ' tab :$quote. ',count(*) from ' tab ';'
  ;
run;
proc sql;
%include code ;
quit;

If you want to try to figure out macro code then make a macro that takes as input a list of names. Then call it with the list of tables to count.

%macro counts(tablist);
%local i tab ;
proc sql;
%do i=1 %to %sysfunc(countw(&tablist,%str( )));
  %let tab=%scan(&tablist,&i,%str( ));
  %if %sysfunc(exist(&tab)) %then %do;
insert into status_tech (table_name,table_count) 
  select "&tab",count(*) from &tab
;
  %end;
%end;
quit;
%mend ;
%counts(fred sam);

If you are talking about checking members in a SAS library then just query to SAS metadata and skip the counting. That is easy if you have the list of names in a macro variable. Preferable already quoted.

%let tablist="fred" "sam";
proc sql;
insert into status_tech (table_name,table_count) 
  select memname,nobs
  from dictionary.tables
  where libname='MYLIB'
    and memname in %upcase(&tablist)
;
quit;