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.
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
Could you share some example data?
Bart
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 |
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
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?
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 |
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
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
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
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 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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.