topic Re: Add variable based on import file name in SAS Programming

Add variable based on import file name

dsm — Mon, 04 Dec 2017 21:55:25 GMT

I have hundreds of comma delimited text files that have two columns and hundreds of thousands of rows, Each file is named after the participant (e.g., 1, 3, 15, etc...). What I need is two-fold, and both may not be possible in the same step.

I'd like to:

1) batch import each file and

2) create a new variable based on each file name. So, I'd like a variable called "subj" that has the value 1 in every row for file 1, and so on...

I've searched around and can't find any information on creating a variable from the import filename. Any advice?

Thanks!

Re: Add variable based on import file name

Reeza — Mon, 04 Dec 2017 21:57:58 GMT

FILEVAR option on the INFILE statement. See example 5 in the documentation.

http://documentation.sas.com/?docsetId=lestmtsref&docsetTarget=n1rill4udj0tfun1fvce3j401plo.htm&docsetVersion=9.4&locale=en

And older walk through

https://support.sas.com/techsup/technote/ts581.pdf

Another option:

https://communities.sas.com/t5/SAS-Communities-Library/How-do-I-write-a-macro-to-import-multiple-text-files-that-have/ta-p/223627

@dsm wrote:

I have hundreds of comma delimited text files that have two columns and hundreds of thousands of rows, Each file is named after the participant (e.g., 1, 3, 15, etc...). What I need is two-fold, and both may not be possible in the same step.

I'd like to:

1) batch import each file and

2) create a new variable based on each file name. So, I'd like a variable called "subj" that has the value 1 in every row for file 1, and so on...

I've searched around and can't find any information on creating a variable from the import filename. Any advice?

Thanks!

Re: Add variable based on import file name

RW9 — Tue, 05 Dec 2017 11:45:18 GMT

Do you want all this data combined into one dataset at the end (which if the data is the same would be the best method):

data want (keep=fname a b);
  infile ".../*.csv" filename=fname;
  input a b;
run;

If you want separate ones then, something like:

filename tmp pipe 'dir ".../*.csv" /b';

data _null_;
  infile tmp dlm="¬";
  call execute(cats('data want',put(_n_,best.),'; infile "',_infile_,'"; input a b; run;'));
run;

This would create wantX with X being incremental for each file - mainly to show how to do it.

Re: Add variable based on import file name

dsm — Mon, 14 May 2018 22:35:31 GMT

super helpful, thanks so much!