Sas7bndx

[HeatherNewton]

Hi i am copying files from sas server to use as text in oracle. Some dataset came with an index file sas7bndx. Am i right to assume that i dont need these index file any more for using text data in oracle?

[SASKiwi]

Sas7bndx files are only of relevance when you are using SAS datasets with SAS software.

[Tom]

---

@HeatherNewton wrote:
Hi i am copying files from sas server to use as text in oracle. Some dataset came with an index file sas7bndx. Am i right to assume that i dont need these index file any more for using text data in oracle?

---

Files?  Do you mean SAS datasets? 

The SAS datasets are the .sas7bdat files.  The .sas7bndx files are the indexes for those file.  You will not need to copy any information out of the indexes.

Note that SAS datasets are not text files that you could just read into Oracle.  So to copy the information out of them into Oracle you will want to use SAS to copy the information. Either directly into Oracle tables or to text files that you could read into Oracle.   And SAS will expect to find the index files in the same directory as the dataset.  So if by "copy files" you mean move them from one SAS server to another SAS server you should probable keep both the dataset and the indexes together.

[HeatherNewton]

If i move sasdataset from sas server A to sas server B then in sas server B, use sas program to convert sas dataset to text then expirt to oracle, then do i need to copy sas index filr from sas server A to sas server B while sas index file is not required in oracle..

[Tom]

---

@HeatherNewton wrote:
If i move sasdataset from sas server A to sas server B then in sas server B, use sas program to convert sas dataset to text then expirt to oracle, then do i need to copy sas index filr from sas server A to sas server B while sas index file is not required in oracle..

---

Test it and see.

1    data class(index=(name));
2      set sashelp.class;
3    run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.CLASS has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds


4
5    data _null_;
6      infile "del ""%sysfunc(pathname(work))\class.sas7bndx""" pipe;
7      input ;
8      put _infile_;
9    run;

NOTE: The infile "del ""C:\Users\xxx\AppData\Local\Temp\1\SAS Temporary Files\_TD1824_yyy_\class.sas7bndx""" is:
      Unnamed Pipe Access Device,
      PROCESS=del "C:\Users\xxx\AppData\Local\Temp\1\SAS Temporary Files\_TD1824_yyy_\class.sas7bndx",
      RECFM=V,LRECL=32767

NOTE: 0 records were read from the infile "del ""C:\Users\xxx\AppData\Local\Temp\1\SAS Temporary
      Files\_TD1824_yyy_\class.sas7bndx""".
NOTE: DATA statement used (Total process time):
      real time           0.08 seconds
      cpu time            0.00 seconds


10
11   data test;
12     set class;
NOTE: File WORK.CLASS.INDEX does not exist.
ERROR: Unable to repair WORK.CLASS.DATA because it is in a scratch library.
13   run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.TEST may be incomplete.  When this step was stopped there were 0 observations and 0 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

Looks like SAS really wants the index file to exist.

It might be possible to run something to tell it to ignore the error, but why not just copy everything?

[HeatherNewton]

Because dataset alone already takes over 1 TB. If copy index too the vol is ever higher.

[Kurt_Bremser]

Remove the index at the source before copying the dataset file.

[Reeza]

Move the index. It speeds up access to the dataset and will speed up your exports and usage of the data and saves you the time you'd likely use by trying to solve it otherwise. The cost of storage is less than the cost of developing and creating a workaround.

Delete the indexes after the conversion is done if you need to maintain the datasets for some reason.

[Reeza]

Building on @Tom example, you can use the DLDMGACTION option to not have the index but note that you cannot do this in WORK libraries.

libname demo '/home/fkhurshed/Demo1';

data demo.class(index=(name));
      set sashelp.class;
   run;



    data _null_;
    fname="tempfile";
    rc=filename(fname, "/home/fkhurshed/Demo1/class.sas7bndx");
    if rc = 0 and fexist(fname) then 
       rc=fdelete(fname); 
    rc=filename(fname);
run;

options DLDMGACTION=noindex;

  data test;
     set demo.class;
run;

I still recommend copying over index and working with the files indexed as it may speed up access. The programming time will outweigh the data storage costs and then you can delete the indexes after, if you need access later, use the option specified.