DATA Step, Macro, Functions and more

How to access and manipulate the content of multiple dbf files?

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

How to access and manipulate the content of multiple dbf files?

Imagine that I have a lot of dbf files in different folders. For example, in d:\test\A, I have af.dbf, sh.dbf, and syhd.dbf. And in d:\test\B, I have ujs.dbf, i.dbf, qye.dbf, and ysuje.dbf. ... So, random large numbers of dbf files are distributed across some random numbers of folders under the d:\test.

For all the dbf.files, there is a field (say, CODE). I want to add a field (say, CODE4), extract the last four digits of the values in CODE, and save them to the CODE4 as character values. So, it looks like as follows:

CODE               CODE4

8273123878       3878

9182787386       7386

0182730099       0099

61267198           7198

...                     ...

How can I automate that process for all the dbf files? Thanks.


Accepted Solutions
Solution
‎05-07-2012 03:41 AM
Super User
Posts: 9,681

Re: How to access and manipulate the content of multiple dbf files?

The simplese way is use PIPE function to gain the power of OS command.

filename x pipe 'dir c:\test\*.txt /s /b'; * list all of txt files under c:\test;
data _null_;
 infile x length=len;
 input fname $varying200. len;
 call execute('proc import datafile="'||fname||'" out='||strip(scan(fname,-2,".\"))||' dbms=dlm replace;getnames=yes;delimiter=" ";run;');
 call execute('data '||strip(scan(fname,-2,".\"))||'; set '||strip(scan(fname,-2,".\"))||';code4=substr(code,length(code)-3);run;');
run;

Ksharp

View solution in original post


All Replies
Solution
‎05-07-2012 03:41 AM
Super User
Posts: 9,681

Re: How to access and manipulate the content of multiple dbf files?

The simplese way is use PIPE function to gain the power of OS command.

filename x pipe 'dir c:\test\*.txt /s /b'; * list all of txt files under c:\test;
data _null_;
 infile x length=len;
 input fname $varying200. len;
 call execute('proc import datafile="'||fname||'" out='||strip(scan(fname,-2,".\"))||' dbms=dlm replace;getnames=yes;delimiter=" ";run;');
 call execute('data '||strip(scan(fname,-2,".\"))||'; set '||strip(scan(fname,-2,".\"))||';code4=substr(code,length(code)-3);run;');
run;

Ksharp

☑ This topic is SOLVED.

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

Discussion stats
  • 1 reply
  • 174 views
  • 0 likes
  • 2 in conversation