topic Re: Creating a data set using files with the same name in many directories in SAS Programming

Creating a data set using files with the same name in many directories

brucehughw — Mon, 09 Feb 2015 20:39:08 GMT

Hello,

I have a data set, call it myds.sas7bdat . I have a two-level directory structure /location/date , and each location/date subdirectory (e.g. ACY/Dec 30) has a myds data set in it. I would like to merge all these data sets. In addition, since there's no location info in the files, I'd like to add a location variable during the merge. Can anyone suggest a way to do this? Macro or data step programming is fine.

Thanks much, Bruce

Re: Creating a data set using files with the same name in many directories

art297 — Mon, 09 Feb 2015 20:42:06 GMT

What operating system are you on and which version of SAS are you using?

Re: Creating a data set using files with the same name in many directories

brucehughw — Mon, 09 Feb 2015 20:50:31 GMT

Windows 7 and SAS 9.2 . thanks.

Re: Creating a data set using files with the same name in many directories

ballardw — Mon, 09 Feb 2015 22:07:28 GMT

First, confirm that these are all SAS data sets.

Re: Creating a data set using files with the same name in many directories

art297 — Mon, 09 Feb 2015 22:36:41 GMT

Bruce,

I dont have SAS 9.2, thus can only provide a partial solution. The following will get all of the files.

You will likely have to create and apply a format to convert the libname paths to their physical names, as well as strip the dsn the get the paths and apply the format to them.

x "cd c:\";

x "dir dec30.sas7bdat /s /b > c:\temp\temp.txt";

data temp (keep=lname path);

infile "c:\temp\temp.txt";

length lname $8;

length path $150;

input;

x=find(_infile_,"\",-200);

lname=catt('xx',_n_);

path=substr(_infile_,1,x-1);

ecmd=cat("libname ",strip(lname),'"',strip(path),'";');

call execute(ecmd);

run;

proc sql noprint;

select lname||".dec30"

into :dpaths

separated by " "

from temp

;

quit;

data want;

set &dpaths. indsname=dsn;

run;

Re: Creating a data set using files with the same name in many directories

Tom — Tue, 10 Feb 2015 01:20:54 GMT

You can ask DOS to generate the list of file names for you.

You can reference the dataset by using the file's name in quotes in a SET statement.

You can use the INDSNAME option to make the name available to your data step so that you can parse out the directory names.

%let path=c:\downloads ;

data files ;

infile "dir /b/s &path\*.sas7bdat" pipe truncover ;

input fname $256.;

run;

proc sql noprint ;

select quote(trim(fname)) into :flist separated by ' '

from files

;

quit;

data want ;

length dsname $256 folder memname $32 ;

set &flist indsname=dsname ;

folder = scan(dsname,-2,'\.') ;

memname = scan(dsname,-1,'\.');

run;

Re: Creating a data set using files with the same name in many directories

brucehughw — Tue, 10 Feb 2015 14:29:08 GMT

Tom,

Thanks for the suggestion. When I run the first part of the code (data files ...), I get an error message: ERROR: Insufficient authorization to access PIPE.

Any idea how to correct this?

Thanks again, Bruce

Re: Creating a data set using files with the same name in many directories

Tom — Wed, 11 Feb 2015 03:04:10 GMT

Your site admins have turned off the ability to call operating system commands from within SAS.

You could run the DOS command in another window and save the output and then just read the output as a simple text file.

Or you could use SAS functions to open the directory and retrieve the file names.

For example look at this old thread.

Also look at this paper that has a nice program for recursively reading the names of the files in a directory.

http://www.wuss.org/proceedings12/55.pdf

Re: Creating a data set using files with the same name in many directories

brucehughw — Wed, 11 Feb 2015 21:11:04 GMT

Tom,

Thanks a lot for the suggestions. I'll try one (or more) and report here if one works. I think I like the paper.

Bruce

Re: Creating a data set using files with the same name in many directories

brucehughw — Thu, 12 Feb 2015 00:09:33 GMT

Hi, Arthur.

Thanks for your suggestion. Sorry I did not respond earlier, but I did not see it. I think I can use part of your code, especially the simple use of DOS commands to create a file list and the SAS code beginning with ecmd. Can you please tell me what that line and the next line do? Ultimately, I want to merge a bunch of datasets with the same name.

I'll post a solution once I have one.

Thanks again, Bruce

Re: Creating a data set using files with the same name in many directories

art297 — Thu, 12 Feb 2015 00:40:56 GMT

Bruce: If you run the first two lines of code from your window's command prompt, it will create the list of files:

cd c:\

dir dec30.sas7bdat /s /b > c:\temp\temp.txt

The following datastep will create all of the libnames:

data temp (keep=lname path);

infile "c:\temp\temp.txt";

length lname $8;

length path $150;

input;

x=find(_infile_,"\",-200);

lname=catt('xx',_n_);

path=substr(_infile_,1,x-1);

ecmd=cat("libname ",strip(lname),'"',strip(path),'";');

call execute(ecmd);

run;

The following proc sql call will create a macro variable that will contain the string that can be used to set all of the files:

proc sql noprint;

select lname||".dec30"

into :dpaths

separated by " "

from temp

;

quit;

Finally, the following step will concatenate all of the files together. The only part I can't help you with, since I don't have 9.2, is regarding what will be returned with the indsname option.

data want;

set &dpaths. indsname=dsn;

run;

Re: Creating a data set using files with the same name in many directories

brucehughw — Thu, 12 Feb 2015 12:51:41 GMT

Arthur,

Thanks so much for sharing your knowledge and time. As soon as I've had a chance to run the code, I'll flag your response with Correct Answer. Looking forward to using this.

thanks again, Bruce

Re: Creating a data set using files with the same name in many directories

data_null__ — Thu, 12 Feb 2015 13:03:17 GMT

Take another look at the suggestion from dated Feb 9, 2015 7:22 PM

Re: Creating a data set using files with the same name in many directories

brucehughw — Thu, 12 Feb 2015 13:12:42 GMT

Hi,

I saw that solution and tried it. The PIPE command (or option) is disabled on my system, unfortunately, and it may not be simple to enable it. I think elements of that solution overlap with others, so I hope to use parts of it.

Thanks, Bruce

Re: Creating a data set using files with the same name in many directories

data_null__ — Thu, 12 Feb 2015 13:31:15 GMT

:smileyshocked:

In the reply to your complaint about PIPE told you how to fix it. You have to create the path list as a file before you run SAS. This nice part of using the path as the "data set name" is that INDSNAME then contains the path so you can create the origin variable you need directly. No need for all those macro variables and LIBREFS.

Re: Creating a data set using files with the same name in many directories

art297 — Thu, 12 Feb 2015 16:05:16 GMT

Bruce: I agree with data_null_ that Tom's code is definitely better than what I had suggested and should be the one you mark as being correct. I didn't realize that SAS now allows full paths in a set statement and their use definitely simplifies identifying the directory names.

Using the same approach I mentioned in my last post, if you run the first two lines of code, below, from your window's command prompt, it will create the list of files:

cd c:\

dir dec30.sas7bdat /s /b > c:\temp\temp.txt

The following datastep will create a file containing all of the desired files and paths:

data files ;

infile "c:\temp\temp.txt" truncover ;

input fname $256.;

run;

The following proc sql call will create a macro variable that will contain the string that can be used to set all of the files:

proc sql noprint ;

select quote(trim(fname)) into :flist separated by ' '

from files

;

quit;

Finally, the following step will concatenate all of the files together including variables that indicate the filenames and their full paths:

data want ;

length dsname $256 folder memname $32 ;

set &flist indsname=dsname ;

folder = scan(dsname,-2,'\.') ;

memname = scan(dsname,-1,'\.');

run;

Re: Creating a data set using files with the same name in many directories

brucehughw — Tue, 24 Feb 2015 16:48:16 GMT

The code Tom sent is mostly working. In the last step I get an error message because the same variable is a character in some files and numeric in others. I know how to fix that for individual files, but would like to fix all the files using a macro. The first two files listed in the &flist macro variable are the following:

"\\VNTSCEX.LOCAL\DFS\SPECIAL\SAS-STORAGE\WILSON, BRUCE\WINTERWX\SAMPLE DATA\ACY\Dec

23\acy12232014_fund.sas7bdat" "\\VNTSCEX.LOCAL\DFS\SPECIAL\SAS-STORAGE\WILSON, BRUCE\WINTERWX\SAMPLE

DATA\ACY\Dec 24\acy12242014_fund.sas7bdat" "\\VNTSCEX.LOCAL\DFS\SPECIAL\SAS-STORAGE\WILSON,

BRUCE\WINTERWX\SAMPLE DATA\ACY\Dec 25\acy12252014_fund.sas7bdat"

Can someone please tell me how to iterate over &flist so that I can open each data set, repair it (I can handle that part), and save it was a new, cleaner data set, named something like acy_12232014_fund_clean (i.e., I just append something to the original name).

Thanks very much, Bruce