Conditionally Proc append

[David_Billa]

Could you please help me understand how to put the table name in quoted
string?

In my code from previous post, INSURANCE is one of the table name value.

[Tom · Accepted solution]

Don't you want to update the values of RUN_ID that are in INSURANCE? Not all of the values or RUN_ID.

Also don't remove leading or embedded spaces from RUN_ID, that could lead to mismatches.

Not sure the impact of trying to use a WHERE= clause on the target of an UPDATE statement.  What would that even mean?

So try code like this:

proc sql;
select quote(trim(RUN_ID)) into :mac separated by ' '
  from INSURANCE
;
quit;

proc sql;
update ifr.details a
  set table_name=(select table_name from INSURANCE b where a.run_id=b.run_id)
    , table_count=(select table_count from INSURANCE b where a.run_id=b.run_id)
  where a.run_id in (&mac)
;
quit;

You can eliminate the macro variable.

proc sql;
update ifr.details a
  set table_name=(select table_name from INSURANCE b where a.run_id=b.run_id)
    , table_count=(select table_count from INSURANCE b where a.run_id=b.run_id)
  where a.run_id in (select b.run_id from INSURANCE b)
;
quit;

Now you can try replacing INSURANCE with a macro variable.

%let tab=INSURANCE;
proc sql;
update ifr.details a
  set table_name=(select table_name from &tab b where a.run_id=b.run_id)
    , table_count=(select table_count from &tab b where a.run_id=b.run_id)
  where a.run_id in (select b.run_id from &tab b)
;
quit;

If that still works then try wrapping it in a macro. 

[Kurt_Bremser]

Corrected SQL, updates only one observation with my previous example data:

proc sql;
update details a
  set
    table_name = (select table_name from insurance b where a.run_id = b.run_id),
    table_count = (select table_count from insurance b where a.run_id = b.run_id)
  where a.run_id in (select run_id from insurance)
;
quit;

To put that into a macro, so you can update from a series of tables:

%macro update(table);
proc sql;
update details a
  set
    table_name = (select table_name from &table. b where a.run_id = b.run_id),
    table_count = (select table_count from &table. b where a.run_id = b.run_id)
  where a.run_id in (select run_id from &table.)
;
quit;
%mend;

Now call that for every table:

data _null_;
input tablename $32.;
call execute(cats('%nrstr(%update(',tablename,'))'));
datalines;
insurance
;

Complete code with data, tested on SAS UE:

data details;
infile datalines truncover;
input Run_ID :$5. Company_code :$3. Datasource :$10. Table_name :$10. table_count;
datalines;
12345 ABC Database	 	 
12346 DEF Excel
;

data insurance;
input Run_ID :$5. Table_name :$10. Table_count;
datalines;
12345 Insurance 87
;

%macro update(table);
proc sql;
update details a
  set
    table_name = (select table_name from &table. b where a.run_id = b.run_id),
    table_count = (select table_count from &table. b where a.run_id = b.run_id)
  where a.run_id in (select run_id from &table.)
;
quit;
%mend;

data _null_;
input tablename $32.;
call execute(cats('%nrstr(%update(',tablename,'))'));
datalines;
insurance
;
  

[David_Billa]

Thanks a ton!
Can we also dynamically delete the WORK datasets which will be created
while the macro executes?

[Tom]

You can use PROC DELETE. Or in SQL you can use DROP TABLE statement.

[David_Billa]

I tired to delete using proc sql; delete from data=&tab within the macro
but it's not deleting.

So I have to use outside the macro by giving all the possible values in
proc sql;
Delete from Insurance, Capital, Risk, etc;
Quitm

[Tom]

Are you deleting tables or observations?

[David_Billa]

@Tom @Kurt_Bremser Thanks for your time. Both of your code worked fine to solve the issues. Thanks again.

[David_Billa]

@Kurt_Bremser @Tom I'm sorry for seeking the help on the same Topic.

@Kurt_Bremser In your tested code, last step which is  data _null_ has values of dataset name in datalines. Assume if you don't know the dataset names in real life, how to do you tackle it? I mean any number of dataset with any name will be created in run time and that's why I have called the dataset names in macro variable as I mentioned in the post.

 I would like to call the dataset names dynamically in call execute or by any other way to accomplish the same output as you create in yoiur code.

[Kurt_Bremser]

From where do you get your dataset names? All datasets in a library, all datasets of a given name in a group of libraries, datasets with a certain substring in their dataset names?

Anyway, use DICTIONARY.TABLES as a source, or SASHELP.VTABLE.

[David_Billa]

I get the dataset names from the library but I don't want all but only few.

Assume, dataset names which may created in the program are, a, b, c, d, e,
f in same library, but we're not sure how many will be created out of these
few. Also there are also other few datasets (e.g. h, I, j, k) will be
created in other programs which we no need worry.

[Kurt_Bremser]

Use a proper where condition to subset from dictionary.tables or sashelp.vtable:

where memname in ("A","B","C","D","E","F")

[David_Billa]

But where can I use this where clause in your tested code in case if you're
creating the datasets in library?

[Tom]

So this code is testing the names in the in-line datalines.

data _null_;
input tablename $32.;
call execute(cats('%nrstr(%update(',tablename,'))'));
datalines;
insurance
;

If instead you have a dataset with the list of table names then you use SET instead of INPUT and DATALINES.  So if your existing dataset is named HAVE and the variable is still named TABLENAME then the code becomes.

data _null_;
  set have ;
  call execute(cats('%nrstr(%update(',tablename,'))'));
run;

If you have the list in a single macro variable.  (Assuming you can't just skip the step that moved the data out of datasets in to the macro variables. If you have the data in the dataset then just use the step above.) So let's assume the macro variable is named FILELIST and the names are separated by spaces. Like this:

%let filelist=insurance healthcare ;

Then the data step becomes:

data _null_;
  length tablename $32 ;
  do i=1 to countw("&filelist",' ');
    tablename=scan("&filelist",i,' ');
    call execute(cats('%nrstr(%update(',tablename,'))'));
  end;
run;

[David_Billa]

Thanks for the suggestion. Will I get any error, if any of the datasets which I listed under Filelist macro variable or in HAVE dataset is not created in the program? If it throws an error, how to tackle this?