topic Re: Unable to update the table dynamically using macro in SAS Programming

Unable to update the table dynamically using macro

David_Billa — Sun, 19 Apr 2020 05:14:05 GMT

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

Re: Unable to update the table dynamically using macro

Kurt_Bremser — Sun, 19 Apr 2020 08:52:43 GMT

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.

Re: Unable to update the table dynamically using macro

Kurt_Bremser — Sun, 19 Apr 2020 11:21:27 GMT

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;

Re: Unable to update the table dynamically using macro

David_Billa — Sun, 19 Apr 2020 11:26:30 GMT

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

Re: Unable to update the table dynamically using macro

Kurt_Bremser — Sun, 19 Apr 2020 11:40:20 GMT

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

Re: Unable to update the table dynamically using macro

Tom — Sun, 19 Apr 2020 13:52:43 GMT

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
;

Re: Unable to update the table dynamically using macro

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

Group by is not required.

Re: Unable to update the table dynamically using macro

Tom — Sun, 19 Apr 2020 14:26:00 GMT

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

Re: Unable to update the table dynamically using macro

David_Billa — Sun, 19 Apr 2020 14:33:36 GMT

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?

Re: Unable to update the table dynamically using macro

Tom — Sun, 19 Apr 2020 14:37:36 GMT

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.

Re: Unable to update the table dynamically using macro

LeonidBatkhan — Tue, 21 Apr 2020 00:53:41 GMT

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.