topic Re: Determine if SAS table is damaged? in SAS Programming

Determine if SAS table is damaged?

Yura2301 — Thu, 09 Oct 2014 11:03:22 GMT

Hi,

Is there some way to define if some SAS table is demaged, without throwing error to the log?

Error looks like:

ERROR: The open failed because library member ...is damaged.

Maybe some sas function or some filter on dictionary.tables etc.

Thanks!

Re: Determine if SAS table is damaged?

RW9 — Thu, 09 Oct 2014 11:12:03 GMT

Hi,

Some questions. What were you running to get that error. What SAS version, OS etc. Have you tried re-starting SAS and trying again. Only help page I could see was: 16086 - MODIFY may generate READ ACCESS VIOLATION and FILE libref.member.DATAIS DAMAGED messages

Re: Determine if SAS table is damaged?

Ksharp — Thu, 09 Oct 2014 12:03:35 GMT

You can use proc datasets to fix it . But as my experience , it is very little chance to fix it all.

libname x v9 'c:\temp';
data x.class;set sashelp.class;run;


proc datasets library=x;
repair class;
quit;

Xia Keshan

Re: Determine if SAS table is damaged?

Ksharp — Thu, 09 Oct 2014 12:05:46 GMT

You can use proc datasets to fix it . But as my experience , it is very little chance to fix it all.

libname x v9 'c:\temp';
data x.class;set sashelp.class;run;


proc datasets library=x;
repair class;
quit;

Xia Keshan

Re: Determine if SAS table is damaged?

Yura2301 — Thu, 09 Oct 2014 12:10:32 GMT

Hi Rw9,

I'm runing under aix, SAS 9.3.

Thanks for the link, will take a look.

Re: Determine if SAS table is damaged?

Yura2301 — Thu, 09 Oct 2014 12:12:48 GMT

Hi Ksharp,

I would like to have possibility just to get list of all demaged tables, for example from some library, I wouldn't repair them for now, just collect all such data sets.

Thanks!

Re: Determine if SAS table is damaged?

Ksharp — Thu, 09 Oct 2014 12:31:00 GMT

Oh. You could try OPEN() , if it return 0 then the table might be ruined .

Re: Determine if SAS table is damaged?

Yura2301 — Thu, 09 Oct 2014 12:49:29 GMT

Hi again,

I thought about that function but it returns 0 also in case if table does not exists physycally.

Maybe there is sence to combine open function with fexist, but again, if open func. return 0 and file exists phisically - is it right rule that define SAS demaged table?

Thanks!

Re: Determine if SAS table is damaged?

Ksharp — Thu, 09 Oct 2014 13:10:02 GMT

you can select these exist tables from dictionary . like :

proc sql;

select memname from dictionary.members where libname='SASHELP' and memtype='DATA' ;

quit;

Re: Determine if SAS table is damaged?

Yura2301 — Thu, 09 Oct 2014 13:28:16 GMT

Hi again,

Yes, sure, so you propose such rule for separating demaged tables:

Exists in dictionary(or sashelp)
Open function returns 0

Ok, will rely on that for now:)

Thanks!

Re: Determine if SAS table is damaged?

Yura2301 — Thu, 09 Oct 2014 15:18:22 GMT

Looks like next simple condition define demaged tab:

%let id=%sysfunc(open(&TableName));

%if %sysfunc(exist(&TableName)) & &id=0 %then %do;

...

%let flag=damaged;

%end;

%else %do;

%let rc=%sysfunc(close(&id));

%end;

But after open dataset it should be closed, so it can be a little bit unsafe if that code will be run on production env., some tables can be needed in exact that moment for someone, maybe for insert/update etc.

As I know open function by default executes with "I" parameter, that is only for read, but still not sure if it will not impact somehow on another possible process on table.

Offcource lock can be done before but it's also bad idea to lock table even to miliseconds on production.

So if there is some way to define if table is demaged without open it- it would be great.

Generally I would like to define such tables without locking them and without puting error to the log.

Thanks!

Re: Determine if SAS table is damaged?

Ksharp — Fri, 10 Oct 2014 11:49:58 GMT

contact sas Technical Support to see if they have a good solution.

Re: Determine if SAS table is damaged?

JonathanWill — Wed, 13 Apr 2022 18:19:48 GMT

OK, I admit this is a pretty ancient thread but I've just had an issue with damaged datasets on a server that ran out of diskspace. I've been trying to answer a very similar question - How can you test programmatically if a dataset is damaged and was surprised how little information there is out there.

My solution is to use the open() function and then check the sysmsg() for the word 'damaged'. The open() function does not automatically put an error into the log (it puts a note), which meets the original requirement.

Here's the macro I've knocked up. I've chosen to output the sysmsg() so I get the error, but it could be done silently. Not sure I want to attempt to repair everything so I've made that optional and I'll probably scan first to see what it finds. Ideally it would record if a repair was successful - not sure if you can get the success of the repair from proc datasets, sysrc may be useful but the definition of 'successful' is open to interpretation - for instance most of the repairs I've seen indicate some data loss.

%macro checkAndRepair(dsn, repair=N);
  %local dsid msg;
  
  %if %sysfunc(exist(&dsn.)) = 0 %then %put WARNING: &dsn does not exist.;
  %else %do;
    %let dsid=%sysfunc(open(&dsn.,i,,D));                                                                                             
    %if (&dsid=0) %then %do;  
      %let msg=%sysfunc(sysmsg());                                                                                                         
      %put &msg.;  
      %if %index(&msg.,damaged) gt 0 %then %do;
        %if &repair. = Y %then %do;
          %put Attempting to fix...;
          proc datasets nolist lib=%scan(&dsn,1,.);
            repair %scan(&dsn,2,.);
          quit;
        %end;
      %end;
    %end; %else                                                                                                                                 
     %put NOTE: &dsn. opened succesfully.;                                                                                                  
    %let dsid=%sysfunc(close(&dsid)); 
  %end;
%mend;