Losing index when updating table with hash replace option

[Mushy]

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

[s_lassen · Accepted solution]

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.

[Mushy]

thanks for helping me out @s_lassen 

[Patrick]

@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.

[Patrick]

@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();