I'm trying to update the table dynamically using the macro below. Log says it is updated, but when I look into the table after refreshing the library, I'm not seeing any values.
Appericiate idf someone of you help me optimize the code below and to sucessfully update the table.
%MACRO STATUS_TECH_UPDATE(tab);
%put &tab;
%put "inside macro";
proc sql;
create table &tab as
select
"&tab" as table_name
,count(1) as table_count
,B.run_id
from test.&tab A, STATUS_TECH B
where A.RUN_ID = B.RUN_ID;
quit;
proc sort data=&tab out=&tab noduprecs;
by _all_;
run;
data &tab;
set &tab;
if run_id=" " then delete;
run;
proc sql;
update test.status_tech
set table_name=(select table_name from &tab),
table_count=(select table_count from &tab);
quit;
%MEND;
data LDIS_TABLES_1;
set FileList_1;
call symput("LD_TABLES",table);
tab=SYMGET('LD_TABLES');
call execute('%STATUS_TECH_UPDATE('||tab||'); ');
run;
Log:
Here 'tab' macro variable resolves to CONTRACT_GROUP
+ proc sql;
7 + create table CONTRACT_GROUP as select "CONTRACT_GROUP" as table_name
,count(1) as table_count ,B.run_id from test.CONTRACT_GROUP A, STATUS_TECH B where A.RUN_ID = B.RUN_ID;
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Compressing data set WORK.CONTRACT_GROUP increased size by 100.00 percent.
Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: Table WORK.CONTRACT_GROUP created, with 14 rows and 3 columns.
8 + quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.07 seconds
cpu time 0.06 seconds
8 + proc sort data=CONTRACT_GROUP out=CONTRACT_GROUP noduprecs; by _all_; run; data
CONTRACT_GROUP; set CONTRACT_GROUP; if run_id=" " then delete; run; proc sql; update
NOTE: There were 14 observations read from the data set WORK.CONTRACT_GROUP.
NOTE: 13 duplicate observations were deleted.
NOTE: The data set WORK.CONTRACT_GROUP has 1 observations and 3 variables.
NOTE: Compressing data set WORK.CONTRACT_GROUP increased size by 100.00 percent.
Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
NOTE: There were 1 observations read from the data set WORK.CONTRACT_GROUP.
89 The SAS System Sunday, April 19, 2020 05:18:00 AM
NOTE: The data set WORK.CONTRACT_GROUP has 1 observations and 3 variables.
NOTE: Compressing data set WORK.CONTRACT_GROUP increased size by 100.00 percent.
Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
9 + test.status_tech set table_name=(select table_name from CONTRACT_GROUP), table_count=(select
table_count from CONTRACT_GROUP);
NOTE: 1 row was updated in test.status_tech.
I repeat I'm not seeing any updated values in test.Status_tech table after refreshing the table and library
... View more
