Repetitive calculations in SAS

[tesu]

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.

[FloydNevseta · Accepted solution]

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;

[tesu]

Thank you. I've learned a lot from you.