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.
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
;
You can use PROC DELETE. Or in SQL you can use DROP TABLE statement.
Are you deleting tables or observations?
@Tom @Kurt_Bremser Thanks for your time. Both of your code worked fine to solve the issues. Thanks again.
@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.
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.
Use a proper where condition to subset from dictionary.tables or sashelp.vtable:
where memname in ("A","B","C","D","E","F")
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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: