Format update with an audit trail

[Althaea]

Greetings,

I want to update a format on a frozen database that has an audit trail on it.

I know ALTER TABLE and Data steps will reset the audit trail which I must avoid. 

Is there a way through programming or other sas tools that would allow me to do this safely ? 

Deleting the format is also a solution if it can be done this way.

Thanks for your help,

Altha

[ballardw]

Please describe your intent for "update a format" in this context. Please remember that FORMAT in SAS often refers to a display format for how to show the value of variables. If you mean something by the structure, such as variable order of data or contents such as the actual variables, then that would not typically be a "format" for many SAS users.

[Althaea]

Hi ballardw,

Thanks for your answer.

I'm referring to format. My variable contains numeric biological values
with character informat.

My issue is that the current format contains special display (for instance
number 8 is for "not done"), but as my biological variable actually
contains numeric values so I want to switch format back to standard
character format.

Altha

[Tom]

You can also just change the format when you USE the data. You can use a FORMAT statement in any proc or data step to attach a different format or remove the existing format.
proc print data=xxx; format Biologic_data ; run;
Note there is no reason to change the INformat for the variable after the dataset has already been created since it has already done its work.

[ballardw · Accepted solution]

---

@Althaea wrote:
Hi ballardw,

Thanks for your answer.

I'm referring to format. My variable contains numeric biological values
with character informat.

My issue is that the current format contains special display (for instance
number 8 is for "not done"), but as my biological variable actually
contains numeric values so I want to switch format back to standard
character format.

Altha

---

This sounds like all you need is a complementary format to Display the values. This is very common:

proc format library=work;
invalue c
'green' = 1
'blue'  = 2
'red'   = 3
;
value c
1 = 'green'
2 = 'blue' 
3 = 'red' 
;
run;

data example;
   input x c.;
datalines;
red
green
blue
;

proc print data=example;
format x c.;
run; 

Note that for any practical purpose the display format can be used. It will create output that displays the given value for almost anything. The above example data set has an INFORMAT but we did not change the dataset to display the desired text values.

I suspect that I have around a 100 of these invalue / value pairs floating around. It is one of the common approaches to get sort orders if I want "green" to appear before "blue" in a graph or report table.

[Tom]

If you can't touch the table the don't.  Perhaps you can meet your goal by creating a new view instead.

Either an SQL view:

proc sql;
  create view newlib.newview as
    select a,b,c format=newformat. 
    from oldlib.olddata 
  ;
quit;

Or a data step view:

data newlib.newview / view=newlib.newview ;
  set oldlib.olddata;
  format c newformat.;
run;

[Althaea]

Tom,
Thanks for your answer.
Indeed I could use that to view the table but I'm aiming for a change as
the data could be reviewed by someone not having the knowledge of sas
format.

I can do whatever doesn't erase the audit trail.

[Tom]

---

@Althaea wrote:
Tom,
Thanks for your answer.
Indeed I could use that to view the table but I'm aiming for a change as
the data could be reviewed by someone not having the knowledge of sas
format.

I can do whatever doesn't erase the audit trail.

---

I have lost track of what the real issue is here.  Why does it matter whether "someone" sees the formatted or raw values?  Can't you just explain to them how the data is structured?

[Althaea]

Those are for a clinical trial study and they might be reviewed at some point and I may not even be there when that happen.

If this is the only solution, i'll leave instruction about how the data has to be read and the format being the wrong one but I was hoping to make the data clean.

I don't have sas right now but to have an idea the variable values would look something like that :

Which can be missleading is a big way and even jeopardize the study as it impact biological critical values aswell.

And I can't reset the audit trail either, would be worse .

Thanks for your time and help!

Edit : But to be honest, I have no idea about how the data are going to be rewiewed. Worries me a bit.

[Althaea]

I'll go for your suggestions and make a different view for when it gets an audit. I'll leave instructions in case.

Thanks for your help !