topic Re: Update base table for every iteration in SAS Programming

Update base table for every iteration

David_Billa — Wed, 22 Apr 2020 03:23:04 GMT

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

Re: Update base table for every iteration

unison — Wed, 22 Apr 2020 06:33:22 GMT

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;

Re: Update base table for every iteration

RichardDeVen — Fri, 24 Apr 2020 16:52:08 GMT

Do you want a missing table_name and table_count because none of the 'insurance' tables had run_id=12345 and company_code="ABF" ?

If you were to process only one 'insurance' table should all the other rows of details be set to have missing values for their run_id and company_code ?

In other words, should the table names and counts in details be set to missing when their runs and companies don't match the 'set' of tables names in the in the filelist? What should happen if a filelisted table contains a new run/company combination ?