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
It would be most helpful if you supplied the datasets involved (as usual, in readily usable form), so we can diagnose your process "live".
When developing such code, you must always start with non-macro code, and only start to make it dynamic once you have verified that it works in all imaginable scenarios.
Why are you storing a value into a macro variable here, only to retrieve it int the next statement?
data LDIS_TABLES_1;
set FileList_1;
call symput("LD_TABLES",table);
tab=SYMGET('LD_TABLES');
call execute('%STATUS_TECH_UPDATE('||tab||'); ');
run;
The only thing this achieves is that new variable tab (if it is in fact new) is now padded to 200 characters.
And without a where condition, your SQL UPDATE would update the whole source dataset, so do this:
proc sql;
update status_tech as a
set
table_name=(select table_name from table as b where a.run_id=b.run_id),
table_count=(select table_count from table as b where a.run_id=b.run_id)
;
quit;
@David_Billa wrote:
Yes, I'm storing a macro variable only to retrieve in the next statement.
Which makes no sense. The value is the same, only the new variable is longer (200 bytes) with more blanks as padding.
Proc sql update without where is not effective here?
Without a condition, the values in ALL observations will be overwritten. I suggest using a data step update herr, anyway; see my answer to your other question.
Don't want a GROUP BY in that first query? I don't thing you need to make new name for this temporary table for every value of &TAB.
create table RECORD_COUNT 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
group by table_name,b.run_id
;
@David_Billa wrote:
Group by is not required.
Then why did you add the step to eliminate the duplicate records?
If you use an aggregate function without group by it is run for the whole table then that one value is remerged onto all of the observations.
Hi David_Billa,
If you want to debug your macro:
1) add the following statement at the beginning of your code:
options symbolgen mprint mlogic;
It will provide more information in SAS log on what is going on.
2) read this blog post: CALL EXECUTE made easy for SAS data-driven programming where you might find that it is likely you need to enclose your macro call in %nrstr() function to delay macro execution.
3) I see no reason why you need call symput and symget, you can use variable table instead of creating new variable tab=SYMGET('LD_TABLES');
Then you last data step will look like:
data LDIS_TABLES_1;
set FileList_1;
call execute('%nrstr(STATUS_TECH_UPDATE('||strip(table)||'));');
run;
Hope this helps.
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: