topic Re: Unable to create an Index file in SAS Programming

Unable to create an Index file

vijayanand — Wed, 06 Nov 2019 12:29:53 GMT

Hi,

Recently we have migrated a sas application from AIX (SAS9.3) to Linux(SAS9.4). In this application, there is a functionality to write a sas dataset with it's index file to an external windows drive. This windows drive is mounted on the AIX and Linux servers.

In the Linux server,when the program is trying to write the sas dataset with it's index on to windows drive, it is throwing the below message.

ERROR: Permanent copy of file TP_OUT.AAAAAAA.INDEX was deleted.
ERROR: Permanent copy of file TP_OUT.AAAAAAA.INDEX was deleted.

The program code is taken from a SAS Data Integration job and submitted as a standalone sas program. The functionality is , if the sas dataset with it's index is available, the process deletes them and creates them and then appends the data using proc append.

Also,in the Windows location, it is shown as locked. TP_OUT.AAAAAAA.SAS7BNDX.LCK

No clue why the index is failing here. Any help is appreciated.

Re: Unable to create an Index file

Shmuel — Wed, 06 Nov 2019 12:36:06 GMT

As much as I know you cannot copy index file into a different OS.

You should recreate the index in the new OS.

Re: Unable to create an Index file

vijayanand — Wed, 06 Nov 2019 13:09:02 GMT

It is actually not being copied...instead is being created on the windows drive using the below code

data TP_OUT.AAAAAAA;
attrib COL1 length = 8 format = YYMMDD10.;
attrib COL2 length = $8;
attrib COL3 length = $10;
attrib COL4 length = $3 format = $3. informat = $3.;
attrib COL5 length = $8;
attrib COL6 length = 8 format = commax18.4 informat = commax18.4;
attrib COL7 length = 8 format = datetime23.;
attrib COL8 length = 8 format = datetime23.;
attrib COL9 length = 8 format = datetime23.;
call missing(of _all_);
stop;
run;

proc datasets library=TP_OUT nolist;
modify AAAAAAA;
ic create not null (COL1);
ic create not null (COL2);
ic create not null (COL3);
ic create not null (COL4);
ic create not null (COL5);
ic create not null (COL6);
ic create not null (COL7);
ic create not null (COL8);
ic create Index1 = primary key (COL1 COL2 COL3 COL4 COL5);
quit;

This code was working fine in SAS9.3 on AIX but failing in SAS9.4 on Linux.

Re: Unable to create an Index file

Kurt_Bremser — Wed, 06 Nov 2019 13:14:34 GMT

@vijayanand wrote:

It is actually not being copied...instead is being created on the windows drive using the below code

data TP_OUT.AAAAAAA;
attrib COL1 length = 8 format = YYMMDD10.;
attrib COL2 length = $8;
attrib COL3 length = $10;
attrib COL4 length = $3 format = $3. informat = $3.;
attrib COL5 length = $8;
attrib COL6 length = 8 format = commax18.4 informat = commax18.4;
attrib COL7 length = 8 format = datetime23.;
attrib COL8 length = 8 format = datetime23.;
attrib COL9 length = 8 format = datetime23.;
call missing(of _all_);
stop;
run;

proc datasets library=TP_OUT nolist;
modify AAAAAAA;
ic create not null (COL1);
ic create not null (COL2);
ic create not null (COL3);
ic create not null (COL4);
ic create not null (COL5);
ic create not null (COL6);
ic create not null (COL7);
ic create not null (COL8);
ic create Index1 = primary key (COL1 COL2 COL3 COL4 COL5);
quit;

This code was working fine in SAS9.3 on AIX but failing in SAS9.4 on Linux.

If code fails, post the log, using the {i} button.

Re: Unable to create an Index file

vijayanand — Wed, 06 Nov 2019 15:21:00 GMT

NOTE: Creating table ...
MPRINT(ETLS_LOADER):   data TP_OUT.AAAAAAA;
MPRINT(ETLS_LOADER):   attrib COL1 length = 8 format = YYMMDD10.;
MPRINT(ETLS_LOADER):   attrib COL2 length = $8 ;
MPRINT(ETLS_LOADER):   attrib COL3 length = $10 ;
MPRINT(ETLS_LOADER):   attrib COL4 length = $3 format = $3. informat = $3. ;
MPRINT(ETLS_LOADER):   attrib COL5 length = $8 label = 'Currency sell code';
22                                                         The SAS System                          09:54 Wednesday, November 6, 2019

MPRINT(ETLS_LOADER):   attrib COL6 length = 8 format = commax18.4 informat = commax18.4 ;
MPRINT(ETLS_LOADER):   attrib COL7 length = 8 format = datetime23. ;
MPRINT(ETLS_LOADER):   attrib COL8 length = 8 format = datetime23. ;
MPRINT(ETLS_LOADER):   attrib COL9 length = 8 format = datetime23. ;
MPRINT(ETLS_LOADER):   call missing(of _all_);
MPRINT(ETLS_LOADER):   stop;
MPRINT(ETLS_LOADER):   run;

NOTE: The data set TP_OUT.AAAAAAA has 0 observations and 9 variables.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.00 seconds
      

MPRINT(ETLS_LOADER):  ;
NOTE: Creating integrity constraints ...
MPRINT(ETLS_LOADER):   proc datasets library=TP_OUT nolist;
MPRINT(ETLS_LOADER):   modify AAAAAAA;
MPRINT(ETLS_LOADER):   ic create not null (COL1);
NOTE: Integrity constraint _NM0001_ defined.
MPRINT(ETLS_LOADER):   ic create not null (COL2);
NOTE: Integrity constraint _NM0002_ defined.
MPRINT(ETLS_LOADER):   ic create not null (COL3);
NOTE: Integrity constraint _NM0003_ defined.
MPRINT(ETLS_LOADER):   ic create not null (COL4);
NOTE: Integrity constraint _NM0004_ defined.
MPRINT(ETLS_LOADER):   ic create not null (COL5);
NOTE: Integrity constraint _NM0005_ defined.
MPRINT(ETLS_LOADER):   ic create not null (COL6);
NOTE: Integrity constraint _NM0006_ defined.
MPRINT(ETLS_LOADER):   ic create not null (COL7);
NOTE: Integrity constraint _NM0007_ defined.
MPRINT(ETLS_LOADER):   ic create not null (COL8);
NOTE: Integrity constraint _NM0008_ defined.
MPRINT(ETLS_LOADER):   ic create Index1 = primary key (COL1 COL2 COL3 COL4 COL5);
ERROR: Permanent copy of file TP_OUT.AAAAAAA.INDEX was deleted.
ERROR: Permanent copy of file TP_OUT.AAAAAAA.INDEX was deleted.
MPRINT(ETLS_LOADER):   quit;

NOTE: Statements not processed because of errors noted above.
ERROR: File TP_OUT.AAAAAAA.INDEX is not open.
NOTE: Statements not processed because of errors noted above.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: SAS set option OBS=0 and will continue to check statements. This might cause NOTE: No observations in data set.
NOTE: PROCEDURE DATASETS used (Total process time):
      real time           0.42 seconds
      cpu time            0.01 seconds
      
MPRINT(ETLS_LOADER):  ;
NOTE: Appending data ...


NOTE: File TP_OUT.AAAAAAA.INDEX does not exist.
ERROR: The open failed because library member TP_OUT.AAAAAAA.DATA is damaged.
MPRINT(ETLS_LOADER):   proc append base = TP_OUT.AAAAAAA data = TP_MART.AAAAAAA () force ;
MPRINT(ETLS_LOADER):   run;

Attached is the snippet of the log for the same issue. TP_OUT is the windows location and TP_MART is the Linux location. Earlier TP_MART was the AIX location.

Re: Unable to create an Index file

Shmuel — Wed, 06 Nov 2019 15:40:07 GMT

Have you noticed message:

The data set TP_OUT.AAAAAAA has 0 observations and 9 variables.

what index can be created if no observations in dataset ?

Re: Unable to create an Index file

vijayanand — Wed, 06 Nov 2019 16:56:19 GMT

The same program ran fine with no errors in AIX. Please find attached the log of the program ran AIX - SAS9.3

NOTE: The data set TP_OUT.AAAAAAA has 0 observations and 9 variables.
NOTE: DATA statement used (Total process time):
      real time           0.05 seconds
      cpu time            0.00 seconds
      

MPRINT(ETLS_LOADER):  ;
NOTE: Creating integrity constraints ...
MPRINT(ETLS_LOADER):   proc datasets library=TP_OUT nolist;
MPRINT(ETLS_LOADER):   modify AAAAAAA;
MPRINT(ETLS_LOADER):   ic create not null (COL1);
NOTE: Integrity constraint _NM0001_ defined.
MPRINT(ETLS_LOADER):   ic create not null (COL2);
NOTE: Integrity constraint _NM0002_ defined.
MPRINT(ETLS_LOADER):   ic create not null (COL3);
NOTE: Integrity constraint _NM0003_ defined.
MPRINT(ETLS_LOADER):   ic create not null (COL4);
NOTE: Integrity constraint _NM0004_ defined.
MPRINT(ETLS_LOADER):   ic create not null (COL5);
NOTE: Integrity constraint _NM0005_ defined.
MPRINT(ETLS_LOADER):   ic create not null (COL6);
NOTE: Integrity constraint _NM0006_ defined.
MPRINT(ETLS_LOADER):   ic create not null (COL7);
NOTE: Integrity constraint _NM0007_ defined.
MPRINT(ETLS_LOADER):   ic create not null (COL8);
NOTE: Integrity constraint _NM0008_ defined.
MPRINT(ETLS_LOADER):   ic create Index1 = primary key (COL1 COL2 COL3 COL4 COL5);
NOTE: Integrity constraint Index1 defined.
MPRINT(ETLS_LOADER):   quit;

NOTE: MODIFY was successful for TP_OUT.AAAAAAA.DATA.
NOTE: PROCEDURE DATASETS used (Total process time):
      real time           1.04 seconds
      cpu time            0.03 seconds
      

MPRINT(ETLS_LOADER):  ;
NOTE: Appending data ...
MPRINT(ETLS_LOADER):   proc append base = TP_OUT.AAAAAAA data = TP_MART.AAAAAAA () force ;
MPRINT(ETLS_LOADER):   run;

NOTE: Appending TP_MART.AAAAAAA to TP_OUT.AAAAAAA.
NOTE: There were 903 observations read from the data set TP_MART.AAAAAAA.
NOTE: 903 observations added.
NOTE: The data set TP_OUT.AAAAAAA has 903 observations and 9 variables.
NOTE: PROCEDURE APPEND used (Total process time):
      real time           0.07 seconds
      cpu time            0.00 seconds

Re: Unable to create an Index file

Shmuel — Wed, 06 Nov 2019 17:57:12 GMT

I have searched google for "sas documentation ERROR: Permanent copy of file ... was deleted".

Please check next links:

1) https://communities.sas.com/t5/SAS-IML-Software-and-Matrix/Cannot-Trouble-Shoot-Error-quot-permanent-copy-deleted-quot/td-p/139965

2) http://support.sas.com/kb/41/488.html

Re: Unable to create an Index file

vijayanand — Wed, 06 Nov 2019 18:16:30 GMT

Thanks for these links Shmuel.

It is the same windows drive that was being used when teh application was running in AIX-SAS9.3.

Now the application is running in Linux SAS9.4 and using the same windows drive...very strange.

It is actually the code generated by SAS DI Studio Table loader transformation with Replace Entire Table as load technique. This code is being submitted in SAS EG as a standalone sas program.

Though the log says that the index "Permanent copy of file TP_OUT.AAAAAAA.INDEX was deleted, the index file (AAAAAAA.sas7bndx.lck) is physically present with a lock.

Re: Unable to create an Index file

Shmuel — Wed, 06 Nov 2019 18:15:54 GMT

Does the file name have suffix .lck ?

Then file is locked and probably damaged.

Using windows tools delete the file and rerun your process.

If it will not help - I hope someone else can help.