The dbflist.txt contains all the 8-digit names of the dbf files in D:\myfolder.
For one dbf file (for example, 00093282.dbf), I run the following code:
libname mylib "D:\myfolder\";
DATA mylib.u107all;
length CODE $ 56.;
infile "D:\myfolder\u107all.csv" dsd dlm=',' firstobs=2;
input CODE $ thevariable;
RUN;
filename m3282 'D:\myfolder\00093282.dbf';
PROC DBF db5=m3282 out=mylib.m3282; run;
PROC SQL;
create table mylib.ma3282 as select
A.CODE, A.thevariable, B.CODE, B.POINT_X, B.POINT_Y
from mylib.u107all A, mylib.m3282 B
where A.CODE = B.CODE;
QUIT;
ods output "Autocorrelation Statistics" = mylib.moran3282;
PROC VARIOGRAM data=mylib.ma3282;
compute novar autoc (weights=distance);
coordinates xc=POINT_X yc=POINT_Y;
var thevariable; run; quit;
ods output close;
Then, I have the mylib.ma3282 data set that contains some numbers of interst.
VarName Assumption Coefficient Observed Expected Std Dev Z Pr > |Z|
thevariable Normality Moran's I -0.00242 -0.00252 0.0000363 2.651 0.008
thevariable Normality Geary's c 1.00023 1 0.0004783 0.491 0.6237
I want to collect the bold numbers in the mylib.ma3282 data set and create "result.txt" as follows:
ma morano morane mz mp gearyo gz gp
3282 -0.00242 -0.00252 2.651 0.008 1.00023 1 0.491 0.6237
5480 ... ... ... ... ... ... ... ...
5520 ... ... ... ... ... ... ... ...
Here, the field "ma" has the values that are the last 4 digits of the name of the dbf files. For 00093282.dbf, ma takes 3282.
How can I do that in SAS?
Thank you.
First off, you can do just about anything with SAS.
Basically, I took your code and wrapped the parts that run the proc dbf and proc variogram in a macro, since that code runs repetitively for each dbf in the folder. I added a data step after the ods output to reformat your ods output then appended it to a results data set that captures the stats for each run. Also, instead of using the list with the file names, I opted to read the file names directly from the folder by piping the dir command. Here's the code.
%let mydir = c:\data\test;
libname mylib "&mydir";
DATA mylib.u107all;
length CODE $ 56.;
infile "&mydir\u107all.csv" dsd dlm=',' firstobs=2;
input CODE $ thevariable;
RUN;
%macro analyze_dbf( fnm );
%let last4 = %substr( %scan( &fnm, 1, . ), 5, 4 );
filename m "&mydir\&fnm";
PROC DBF db5=m out=mylib.m&last4;
run;
PROC SQL;
create table mylib.ma&last4 as select
A.CODE, A.thevariable, B.CODE, B.POINT_X, B.POINT_Y
from mylib.u107all A, mylib.m&last4 B
where A.CODE = B.CODE;
QUIT;
ods output "Autocorrelation Statistics" = mylib.moran&last4;
PROC VARIOGRAM data=mylib.ma&last4;
compute novar autoc (weights=distance);
coordinates xc=POINT_X yc=POINT_Y;
var thevariable;
run;
quit;
ods output close;
data stats;
ma = &last4;
do until ( done );
set mylib.moran&last4 end=done;
if coefficient =: 'Moran' then do;
morano = observedvalue;
morane = expectedvalue;
mz = z;
mp = pvalue;
end;
else if coefficient =: 'Geary' then do;
gearyo = observedvalue;
gz = z;
gp = pvalue;
end;
end;
output;
keep ma morano--gp;
run;
proc append base=mylib.results data=stats;
run;
%mend;
filename x pipe "dir /b &mydir\*.dbf";
data _null_;
infile x;
input fnm :$15.;
call execute ( cats( '%analyze_dbf(', strip(fnm), ');' ));
run;
First off, you can do just about anything with SAS.
Basically, I took your code and wrapped the parts that run the proc dbf and proc variogram in a macro, since that code runs repetitively for each dbf in the folder. I added a data step after the ods output to reformat your ods output then appended it to a results data set that captures the stats for each run. Also, instead of using the list with the file names, I opted to read the file names directly from the folder by piping the dir command. Here's the code.
%let mydir = c:\data\test;
libname mylib "&mydir";
DATA mylib.u107all;
length CODE $ 56.;
infile "&mydir\u107all.csv" dsd dlm=',' firstobs=2;
input CODE $ thevariable;
RUN;
%macro analyze_dbf( fnm );
%let last4 = %substr( %scan( &fnm, 1, . ), 5, 4 );
filename m "&mydir\&fnm";
PROC DBF db5=m out=mylib.m&last4;
run;
PROC SQL;
create table mylib.ma&last4 as select
A.CODE, A.thevariable, B.CODE, B.POINT_X, B.POINT_Y
from mylib.u107all A, mylib.m&last4 B
where A.CODE = B.CODE;
QUIT;
ods output "Autocorrelation Statistics" = mylib.moran&last4;
PROC VARIOGRAM data=mylib.ma&last4;
compute novar autoc (weights=distance);
coordinates xc=POINT_X yc=POINT_Y;
var thevariable;
run;
quit;
ods output close;
data stats;
ma = &last4;
do until ( done );
set mylib.moran&last4 end=done;
if coefficient =: 'Moran' then do;
morano = observedvalue;
morane = expectedvalue;
mz = z;
mp = pvalue;
end;
else if coefficient =: 'Geary' then do;
gearyo = observedvalue;
gz = z;
gp = pvalue;
end;
end;
output;
keep ma morano--gp;
run;
proc append base=mylib.results data=stats;
run;
%mend;
filename x pipe "dir /b &mydir\*.dbf";
data _null_;
infile x;
input fnm :$15.;
call execute ( cats( '%analyze_dbf(', strip(fnm), ');' ));
run;
Thank you. I've learned a lot from you.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.