topic Re: Sas7bndx in SAS Programming

Sas7bndx

HeatherNewton — Fri, 21 Oct 2022 02:44:10 GMT

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?

Re: Sas7bndx

SASKiwi — Fri, 21 Oct 2022 03:04:52 GMT

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

Re: Sas7bndx

Tom — Fri, 21 Oct 2022 03:33:34 GMT

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

Re: Sas7bndx

HeatherNewton — Fri, 21 Oct 2022 03:56:20 GMT

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

Re: Sas7bndx

Tom — Fri, 21 Oct 2022 04:54:48 GMT

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

Re: Sas7bndx

HeatherNewton — Fri, 21 Oct 2022 17:12:57 GMT

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

Re: Sas7bndx

Kurt_Bremser — Fri, 21 Oct 2022 17:34:40 GMT

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

Re: Sas7bndx

Reeza — Fri, 21 Oct 2022 17:55:48 GMT

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.

Re: Sas7bndx

Reeza — Fri, 21 Oct 2022 17:58:19 GMT

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.