Looping issue while update target tables based on the previous dataset names

[David_Billa]

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

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

data insurancf;
input Run_ID :$5. Table_name :$10. Table_count;
datalines;
12345 Insurancf 870
;

data insurancg;
input Run_ID :$5. Table_name :$10. Table_count;
datalines;
12345 Insurancg 8700
;

data insuranch;
input Run_ID :$5. Table_name :$10. Table_count;
datalines;
12345 Insuranch 87000
;

%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
Insurancf
Insurancg
Insuranch
;
run;

I've the code as above. If you look at the last data_null_ I have given the values of dataset names which was created before.

But in the real life it may create any number of datasets and so I don't want to hard code the dataset names instead I would like to pass the macro variables which has the values of dataset names as Input to call execute so that it will run for each of the Input dataset.

Here one hint which we can utilize is, Input names will comes under any of these five names or all five (Insurance,Insuarncd,Insurance,Insurancef,Insuaranceg)

Excepted Output is,

Run_ID	Company_code	Datasource	Table_name	table_count
12345	ABC	Database	Insurance	87
12345	ABD	Database	Insurancf	870
12345	ABE	Database	Insurancg	8700
12345	ABF	Database	Insuranch	87000
12346	DEF	Excel

But Output what I got based on my code is

Run_ID	Company_code	Datasource	Table_name	table_count
12345	ABC	Database	Insuranch	87000
12345	ABD	Database	Insuranch	87000
12345	ABE	Database	Insuranch	87000
12345	ABF	Database	Insuranch	87000
12346	DEF	Excel

[yabwon]

Hi,

Your result is 100% expected behaviour, because you have NonUnique update keys. 

Try to add table name variable to your basetable(`details`) so you will be able to find 1-to-1 match between "basetable" and the one with count.

Bart

[David_Billa]

Is there a way, please help me with the example or help by tweaking my code?

[Kurt_Bremser]

What is the rule that tells you that the data from insurancf must overwrite company ABD, but not the others? (and so on for the other datasets)

[David_Billa]

There is no rule for company code. I just want to get the table names and table count from the datasets which was created before

[Kurt_Bremser]

Without a rule, there is no algorithm, and without an algorithm, there can be no code.

---

@David_Billa wrote:

There is no rule for company code. I just want to get the table names and table count from the datasets which was created before

---

[David_Billa]

There is a rule as I mentioned in the previous posts. But no rule for
company code. Why would you except rule for company code? I just need to
retain the value for company code which was already available in base table
(details) as it is same for all records for now.

Can't we tweak the proposed code to get the table names and table count
dynamically and insert in base table (details) without the rule for
company code?

[Kurt_Bremser]

The only difference between the observations for run_id is in the company_code, so I assumed this to be the deciding factor.

What then, is the deciding factor that causes the value from (e.g.) insuranch to only overwrite this specific observation it ends up in, and not the others?

[David_Billa]

Well, let me slightly tweak the requirement. How can we handle if the table
name and company code is unique? Assume the table name and company code
is different for each record.

[Kurt_Bremser]

You have not answered my question.

WHAT IS THE RULE?

[David_Billa]

Rule is simple. Assume two datasets are created called A and B in the
program and it has Run_id as one of the field

Assume you also have one base table (details) which has all the fields as
I mentioned in the initial post. Field company code is unique.

Now you want to get the dataset name and count of the respective datasets
(A & B) and update in base table 'details'.

To answer your question, company code value should be different for A and
B while we update base table.

[yabwon]

data details;
infile datalines truncover;
input Run_ID :$5. Company_code :$3. Datasource :$10. Table_name :$10. table_count;
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;
datalines;
ABC Insurance 87
;

data insurancf;
input Company_code :$5. Table_name :$10. Table_count;
datalines;
ABD Insurancf 870
;

data insurancg;
input Company_code :$5. Table_name :$10. Table_count;
datalines;
ABE Insurancg 8700
;

data insuranch;
input Company_code :$5. Table_name :$10. Table_count;
datalines;
ABF Insuranch 87000
;

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

data _null_;
input tablename $32.;
call execute(cats('%nrstr(%update(',tablename,'))'));
datalines;
Insurance
Insurancf
Insurancg
Insuranch
;
run;

proc print data = details;
run;

[David_Billa]

What if I use both run_id and company code for the join condition?

[yabwon]

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

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

data insurancf;
input Run_ID :$5. Company_code :$5. Table_name :$10. Table_count;
datalines;
12345 ABD Insurancf 870
;

data insurancg;
input Run_ID :$5. Company_code :$5. Table_name :$10. Table_count;
datalines;
12345 ABE Insurancg 8700
;

data insuranch;
input Run_ID :$5. Company_code :$5. Table_name :$10. Table_count;
datalines;
12345 ABF Insuranch 87000
;

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

data _null_;
input tablename $32.;
call execute(cats('%nrstr(%update(',tablename,'))'));
datalines;
Insurance
Insurancf
Insurancg
Insuranch
;
run;

proc print data = details;
run;