I've the data and code as follows. I want to update the base table for every Iteration, but it seems there is an issue while updating the base table with my code.Instead of updating the base table with every Iteration, it is replacing the results in the base table for matching observations. Request your help here.
Code which I have is,
data details; infile datalines truncover; input Run_ID :5. Company_code :$3. Datasource :$10. Table_name :$10. table_count : 2.; datalines; 12345 ABC Database 12345 ABD Database 12345 ABE Database 12345 ABF Database 12346 DEF Excel ; data insurance; input Company_code :$5. Table_name :$10. Table_count :2. Run_ID :5.; datalines; ABC Insurance 87 12345 ; data insurancf; input Company_code :$5. Table_name :$10. Table_count :2. Run_ID :5.; datalines; ABD Insurancf 88 12345 ; data insurancg; input Company_code :$5. Table_name :$10. Table_count :2. Run_ID :5.; datalines; ABE Insurancg 89 12345 ; data insuranch; input Company_code :$5. Table_name :$10. Table_count :2. Run_ID :5.; datalines; DEF Insuranch 90 12346 ; %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 b.run_id from &table. as b) ; quit; %mend; %let filelist=insurance insurancf insurancg insuranch ; data _null_; length tablename $32 ; do i=1 to countw("&filelist",' '); tablename=scan("&filelist",i,' '); call execute(cats('%nrstr(%update(',tablename,'))')); end; run;
Result which I got is,
Run_ID Company_code Datasource Table_name table_count
12345 ABC Database Insurancg 89
12345 ABD Database Insurancg 89
12345 ABE Database Insurancg 89
12345 ABF Database Insurancg 89
12346 DEF Excel Insuranch 90
Desired result is,
Run_ID Company_code Datasource Table_name table_count
12345 ABC Database Insurance 87
12345 ABD Database Insurancf 88
12345 ABE Database Insurancg 89
12345 ABF Database
12346 DEF Excel Insuranch 90
Small adjustments to match on company_code as well:
%macro update(table);
proc sql;
update details a
set
table_name = (select table_name from &table. b where a.run_id=b.run_id and a.company_code = b.company_code),
table_count = (select table_count from &table. b where a.run_id=b.run_id and a.company_code = b.company_code)
where cats(a.run_id,a.company_code) in (select cats(b.run_id,b.company_code) from &table. as b)
;
quit;
%mend;
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: