DATA Step, Macro, Functions and more

Add variable based on import file name

Accepted Solution Solved
Reply
Occasional Contributor dsm
Occasional Contributor
Posts: 6
Accepted Solution

Add variable based on import file name

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!


Accepted Solutions
Solution
‎05-14-2018 06:35 PM
Super User
Super User
Posts: 9,407

Re: Add variable based on import file name

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.

View solution in original post


All Replies
Super User
Posts: 23,262

Re: Add variable based on import file name

[ Edited ]

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

 

http://documentation.sas.com/?docsetId=lestmtsref&docsetTarget=n1rill4udj0tfun1fvce3j401plo.htm&docs...

 

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

 


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!


 

Solution
‎05-14-2018 06:35 PM
Super User
Super User
Posts: 9,407

Re: Add variable based on import file name

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.

Occasional Contributor dsm
Occasional Contributor
Posts: 6

Re: Add variable based on import file name

super helpful, thanks so much!
☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 229 views
  • 2 likes
  • 3 in conversation