Unable to update the table dynamically using macro

[David_Billa]

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

[Kurt_Bremser]

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.

[Kurt_Bremser]

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]

Yes, I'm storing a macro variable only to retrieve in the next statement.
Proc sql update without where is not effective here?

[Kurt_Bremser]

---

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

[Tom]

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]

Group by is not required.

[Tom]

---

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

[David_Billa]

Ok,if I use group by then can I get rid of the issue which you mentioned?

Can I understand I just have to use group by additionally as you mentioned
without removing anything which includes proc sort which I used to delete
duplicate records?

[Tom]

Whether the counts are what you want without the group by depends on what you intended to count.
Note that you can add the DISTINCT keyword to your SQL SELECT statement to remove the duplicate rows without a separate step.

[LeonidBatkhan]

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.