BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tesu
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
FloydNevseta
Pyrite | Level 9

First off, you can do just about anything with SAS. Smiley Happy

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;

View solution in original post

2 REPLIES 2
FloydNevseta
Pyrite | Level 9

First off, you can do just about anything with SAS. Smiley Happy

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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 786 views
  • 0 likes
  • 2 in conversation