topic Re: update large table based another table key in SAS Programming

update large table based another table key

ger15xxhcker — Thu, 16 Jun 2022 18:11:52 GMT

Hi!

I have a table called ID_TABLE that contains only IDs. ID_ONE is the name of the column.There is another table called FULL_TABLE which has an ID_TWO column and two other columns called A1 and B1.

If the value of the ID_ONE field is equal to the ID_TWO field, the value of fields A1 and B1 in the FULL_TABLE table must be overwritten with an 'X'.

Table A has about 30,000 rows. Table B has 40,000,000 rows.

How can this be solved with the best performance? Hash joinnal? Proc sql with very slow. The board itself should be overwritten.

Thanks

Re: update large table based another table key

JOL — Thu, 16 Jun 2022 18:31:21 GMT

You can try this:

/*Sort source tables first so you can do a Data Step Merge*/
proc sort data = ID_TABLE out=work.ID_TABLE;
by ID_ONE;
run;

proc sort data = FULL_TABLE out=work.FULL_TABLE;
by ID_TWO;
run;

/*Use the Data Step to merge and create desired output in a new table*/
data newtable;
merge work.ID_TABLE (IN=IT) work.FULL_TABLE(IN=FT RENAME=(ID_TWO=ID_ONE));
by ID_ONE;

if IT = 1 and FT=1 then
do;
A1 = 'X';
B1 = 'X';
end;
run;

Re: update large table based another table key

Kurt_Bremser — Thu, 16 Jun 2022 21:01:18 GMT

Hash object:

data want;
set b;
if _n_ = 1
then do;
  declare hash a (dataset:"a (rename=(id_one=id_two))");
  a.definekey("id_two");
  a.definedone();
end;
if a.check() = 0
then do;
  a1 = "X";
  b1 = "X";
end;
run;

Re: update large table based another table key

ger15xxhcker — Fri, 17 Jun 2022 18:48:22 GMT

Thanks! That's exactly what I was thinking! I learned a lot from it. Do you know how I could extract from this hash update how many successful updates there were in the target table?

Re: update large table based another table key

Kurt_Bremser — Fri, 17 Jun 2022 20:21:41 GMT

Keep a running count, and store it in a macro variable for later use:

data want;
set b end=done;
if _n_ = 1
then do;
  declare hash a (dataset:"a (rename=(id_one=id_two))");
  a.definekey("id_two");
  a.definedone();
end;
if a.check() = 0
then do;
  a1 = "X";
  b1 = "X";
  count + 1;
end;
if done
then do;
  put count=;
  call symputx('count',count);
end;
drop count;
run;

Re: update large table based another table key

ger15xxhcker — Mon, 20 Jun 2022 23:31:05 GMT

Thanks!

If I want to update table' b' locally instead of 'want' table and there is an index on the 'b' table, will it be dropped or retained? So after the data there will be the ' b' table and after the set there will be also the 'b' table.

Re: update large table based another table key

Kurt_Bremser — Thu, 23 Jun 2022 07:21:58 GMT

After recreating a dataset, you must also recreate the index.

Run this simple example and look at the print output:

data class;
set sashelp.class;
run;

proc sql;
create index sex on work.class(sex);
quit;

proc datasets lib=work;
quit;

data class;
set class;
x = 1;
run;

proc datasets lib=work;
quit;