topic Re: Losing index when updating table with hash replace option in SAS Programming

Losing index when updating table with hash replace option

Mushy — Wed, 06 Mar 2024 09:59:39 GMT

Hello All,

I am using the below logic to update the fact table, but I am losing index... do we have a method where we repace the required rows and also retain index..?

data fact;
if 0 then set dim fact;
if _n_=1 then do;
declare hash h1(dataset:'dim(where=( roll=1))');
h1.definekey('roll','roll1');
h1.definedata(all:'yes');
h1.definedone();
end;
set fact;
if roll<3 then do;
if h1.find()=0 then h1.replace();
end;
run;

Thanks,

Mushy

Re: Losing index when updating table with hash replace option

s_lassen — Wed, 06 Mar 2024 10:18:29 GMT

You can use the MODIFY statement instead of the SET statement.

So it could be done like

data fact;
  if 0 then
    set dim;
  if _n_=1 then do;
    declare hash h1(dataset:'dim(where=( roll=1))');
    h1.definekey('roll','roll1');
    h1.definedata(all:'yes');
    h1.definedone();
    end;
  modify fact;
  if roll<3 then do;
    if h1.find()=0 then h1.replace();
    replace fact;
    end;
run;

I put the REPLACE statement inside IF block as no variables are changed if ROLL>=3, so the observation stays the same.

Re: Losing index when updating table with hash replace option

Patrick — Wed, 06 Mar 2024 10:55:30 GMT

@Mushy You can use the data step modify or update statement to update a SAS table in place which @s_lassen has already demonstrated. BUT... based on your code you want also to add variables as well - the one from the dimension table as I understand it - and then an update in place won't work anymore unless you first add the additional columns with a SQL Alter Table.

The by far easiest way is to just create a new table including creation of the indexes.

Looking at the code you're sharing and besides that a fact table shouldn't require updates I have some questions what you're actually trying to achieve.

Given your code:

data fact;
  if 0 then set dim fact;
  if _n_=1 then
    do;
      declare hash h1(dataset:'dim(where=( roll=1))');
      h1.definekey('roll','roll1');
      h1.definedata(all:'yes');
      h1.definedone();
    end;

  set fact;

  if roll<3 then
    do;
      if h1.find()=0 then h1.replace();
    end;
run;

This loads a hash with rows where ROLL always got a value of 1

declare hash h1(dataset:'dim(where=( roll=1))');

Then what's the purpose of below if ROLL is always 1 in the hash

if roll<3 then

And below the replace doesn't make any sense. If the condition is true then the variables in the data set will be overwritten with the values from the hash. The replace() method comes after and at the moment when the values in the data set (the pdv) and the matching item in the hash table have the same values - so replace() makes no sense.

if h1.find()=0 then h1.replace();

Re: Losing index when updating table with hash replace option

Mushy — Wed, 06 Mar 2024 10:55:34 GMT

thanks for helping me out @s_lassen

Re: Losing index when updating table with hash replace option

Patrick — Wed, 06 Mar 2024 10:59:23 GMT

@s_lassen That can't be the solution. And update in place can't change the table structure and though either IF 0 THEN SET DIM; is not required or more likely this code is not going to do what's intended.