DATA Step, Macro, Functions and more

how to fetch SAS dataset

Accepted Solution Solved
Reply
Contributor
Posts: 52
Accepted Solution

how to fetch SAS dataset

Hi,

I have to fetch the SAS dataset from XYZ location out of the SAS Server which is having N number of folders and Those folders contains N numbers of sub-folders.Sub-folders contains different extension Files including SAS,therefore I need to fetch Only SAS file from every folders as well sub-folder and need copy to some ABC Location out of the SAS server in One Folder.

Your help will appreciate. Thanks in advance.


Accepted Solutions
Solution
‎05-20-2014 06:29 AM
Super User
Super User
Posts: 7,400

Re: how to fetch SAS dataset

So, I have two files in: s:\temp\rob\xyz, ABC.txt and ABC2.txt (it doesn't matter what the file extension is, they could just as easily be .sas or .sas7bdat the process is the same).  Then I have a subdirectory s:\temp\rob\xyz\dgh with two files ERT.txt, ERT2.txt.  I then submit this program in SAS:

filename tmp pipe 'dir "S:\Temp\Rob\XYZ" /s /b';

data dir;
  length buffer $2000.;
  infile tmp;
  input buffer $2000.;
  command="x 'copy "||'"'||strip(buffer)||'" "s:\temp\rob"'||"';";
  call execute(command);
run;

This reads in the directory listing for XYZ with all subdirectories, bare format so only path\file.  I then call execute a system command to copy each one to a new folder s:\temp\rob.  The x 'copy "..." "..."'; is a dos command to copy a file (of any type) to a new location.  I feed it with the directory listing.  You can modify the above to only process SAS programs or datasets with a simple if then else.

View solution in original post


All Replies
Super User
Posts: 5,256

Re: how to fetch SAS dataset

If you are speaking of physical folders, and Base SAS data sets, you need some UNIX/Windows scripting expert to help you fins all locations.

If you are lucky, there exist autoexec.sas-files or similar with libref statements which lets you fins your data locations.

Data never sleeps
Super User
Super User
Posts: 7,400

Re: how to fetch SAS dataset

Hi,

Have a search on here for directory listings, e.g.

filename mypipe 'dir "..."';

data temp;

     infile mypipe...;

run;

Basically you send a DOS directory command output back into a dataset where you can pull out directories/files etc. and then use that further in your processing.

Contributor
Posts: 52

Re: how to fetch SAS dataset

Hi,

As per your suggestion its completely new concept for me. Could you please elaborate it with example, how to fetch SAS Codes from one location within sub-folders and copy to another location all codes in one folder so it would be easy for me to understand.

Contributor
Posts: 52

Re: how to fetch SAS dataset

Hi RW9,

I was trying to catch your logic Initially got the success but while copying part I got failed. Below was my try.


%global  pt ;

%let pt = 'dir "XYZ" /s';

%put &pt.;

filename DIRLIST1 pipe &pt;

Data dirlist ;

     length buffer $256 ;

     infile dirlist1 length=reclen ;

     input buffer $varying256. reclen ;

Run ;

Data path_file;

  set dirlist;

  where index(strip(upcase(buffer)),'.SAS') > 0 ;*and scan(strip(upcase(buffer)),1,"") ^= "DIRECTORY" ;

  num = 1;

Run;

libname dishant "ABC" ;

proc datasets memtype=(data program);

   copy out=dishant;

      select path_file ;

run;

As per Code In my path_file Dataset I have all SAS File Now want to copy those all files into ABC location Instead of path_file Dataset Contains file. Please Help Me on copying the files. Thanks In advance.

Super User
Posts: 6,932

Re: how to fetch SAS dataset

In the buffer variable, you have the whole path name of the files you searched for. You now have to extract the directory path (everything up to the last \), which you later use to assign a libname to, so you can use that libname as the source lib in the proc datasets procedure. You also extract the filename itself, strip the .sas7bdat and get the logical SAS member name of the dataset. This is what you use in the select statement of proc datasets.

What it amounts to is a lot of call execute or macro processing.

Or you do it with the help of the operating system.

In UNIX it would look like that:

find $PATH -type f -name \*.sas7bdat -exec cp {} $TARGET \;

I'd assume that windows has a usable equivalent of the find command by now.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 6,932

Re: how to fetch SAS dataset

A word of caution: memtype=program does not cover your ordinary .sas files, which are basically plain text, but precompiled SAS programs!

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,400

Re: how to fetch SAS dataset

So, assuming you have buffer that looks something like:

Buffer

s:\temp\somedirectory\Aprog.sas

s:\temp\somedirectory\Bprog.sas

s:\temp\somedirectory\subdir\data\abc.sas7bdat

Then you could just generate the code from there - assumes the directories to copy to exist!:

data dirlist;

     attrib commandstr format=$2000.;

     if index(buffer,"data")>0 then commandstr='copy "'||strip(buffer)||'" "s:\abc\data"';

     else commandstr='copy "'||strip(buffer)||'" "s:\abc\"';

     call execute('x commandstr');

run;

/* Note - untested, but should be similar */

Contributor
Posts: 52

Re: how to fetch SAS dataset

Hi all,

In my Buffer my data will look like below,

Buffer

08/05/2012  06:35           214,016 tblsystem.sas7bdat

06/05/2014  08:03             4,987 DER_CAFQ056A2212_188669.sas

06/05/2014  06:35            13,312 tblcountryregions.sas7bdat

Above Bold characters is my SAS Code in XYZ location those Code Names Showing in Buffer path_file dataset , want to copy all code which is in Buffer Variable to ABC location With Contains Of that code respective.

Please revert back me on this.

Super User
Super User
Posts: 7,400

Re: how to fetch SAS dataset

So this code:

filename tmp pipe 'dir "s:\temp\rob" /s';

data a;
  length buffer $2000;
  infile tmp;
  input buffer $2000.;
run;

Will yield something like:

buffer

Volume Serial Number is xxxxx               /* Drop this */

Directory of s:\temp\temp                        /* Drop the Directory of, using tranwrd, and retain this as path-to */

15/02/2014  16:34  <DIR> ...                    /* Drop this */

13/02/2014   12:34  adataset.sas7bdat               /* substr(scan(xx,2," ")+1) = filename.

...

You will then have:

pathto                     file

s:\temp\temp          adataset.sas7bdat

And its just a matter of copy "pathto\file" "s:abc"

Contributor
Posts: 52

Re: how to fetch SAS dataset

RW9,

matter of copy "pathto\file" "s:abc"only giving the problem,Can you please give example for Better.


My Final data Is Like Below.


Filename                                        FileLoc

tblcountryregions.sas7bdat     \\s:\abc\ISCR

tblsystem.sas7bdat               \\s:\abc\ISCR\181395\A

HAA00616_DER.sas              \\s:\abc\ISCR\181396


As per My criteria I want All File In "\\T:\xyz" Location.How?

Contributor
Posts: 52

Re: how to fetch SAS dataset

RW9,

As Above message I Need all File In "\\T:\xyz" Location Which look Like Below,

tblcountryregions.sas7bdat

tblsystem.sas7bdat

tblsystem.sas7bdat



On Clicking of respective file it should contains Respective material for respective files. How?

Contributor
Posts: 52

Re: how to fetch SAS dataset

RW9,

Its Containing N Number Of Files At N number of folders and sub-folders,want to fetch those Only SAS File and need to Copy "\\T:\xyz" location,Therefore In my Filename Have Only SAS File With Respective FileLoc and finally need to copy all those file to above location.How?

Please revert me back on this to achieve final step of destination.

Solution
‎05-20-2014 06:29 AM
Super User
Super User
Posts: 7,400

Re: how to fetch SAS dataset

So, I have two files in: s:\temp\rob\xyz, ABC.txt and ABC2.txt (it doesn't matter what the file extension is, they could just as easily be .sas or .sas7bdat the process is the same).  Then I have a subdirectory s:\temp\rob\xyz\dgh with two files ERT.txt, ERT2.txt.  I then submit this program in SAS:

filename tmp pipe 'dir "S:\Temp\Rob\XYZ" /s /b';

data dir;
  length buffer $2000.;
  infile tmp;
  input buffer $2000.;
  command="x 'copy "||'"'||strip(buffer)||'" "s:\temp\rob"'||"';";
  call execute(command);
run;

This reads in the directory listing for XYZ with all subdirectories, bare format so only path\file.  I then call execute a system command to copy each one to a new folder s:\temp\rob.  The x 'copy "..." "..."'; is a dos command to copy a file (of any type) to a new location.  I feed it with the directory listing.  You can modify the above to only process SAS programs or datasets with a simple if then else.

Contributor
Posts: 52

Re: how to fetch SAS dataset

RW9,

Sorry for more update but last to want fetch only .sas File not .sas7bdat, since as per your code its fetching both files.How to avoid that?

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 16 replies
  • 517 views
  • 6 likes
  • 5 in conversation