topic Re: Looping issue while update target tables based on the previous dataset names in SAS Programming

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

David_Billa — Tue, 21 Apr 2020 16:21:29 GMT

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

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

David_Billa — Tue, 21 Apr 2020 16:30:08 GMT

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

yabwon — Tue, 21 Apr 2020 16:25:20 GMT

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

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

Kurt_Bremser — Tue, 21 Apr 2020 16:28:26 GMT

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)

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

David_Billa — Tue, 21 Apr 2020 16:29:14 GMT

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

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

David_Billa — Tue, 21 Apr 2020 16:32:07 GMT

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

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

Kurt_Bremser — Tue, 21 Apr 2020 16:57:04 GMT

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

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

David_Billa — Tue, 21 Apr 2020 17:03:36 GMT

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?

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

Kurt_Bremser — Tue, 21 Apr 2020 17:50:42 GMT

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?

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

David_Billa — Tue, 21 Apr 2020 18:02:36 GMT

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.

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

Kurt_Bremser — Tue, 21 Apr 2020 18:10:21 GMT

You have not answered my question.

WHAT IS THE RULE?

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

David_Billa — Tue, 21 Apr 2020 18:27:36 GMT

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.

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

yabwon — Tue, 21 Apr 2020 18:43:14 GMT

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;

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

Kurt_Bremser — Tue, 21 Apr 2020 18:48:48 GMT

You still have not explained why (to follow your initial example) the data from insuranch goes into the 4th observation for run_id 12345, and not into the first, second or third.

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

David_Billa — Tue, 21 Apr 2020 23:33:36 GMT

That's a dummy data and I forgot to change the company code for each of the
table_name.

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

David_Billa — Tue, 21 Apr 2020 23:45:36 GMT

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

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

yabwon — Wed, 22 Apr 2020 15:10:12 GMT

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;