How to use data rows instead of "cards" in a loop in SAS?

Question

I have a basic problem with SAS. I have a code block that calculates three values within the 6 variables given as a "CARD" in SAS. With every card line, I need to run the whole code again and again. I sholud do this over 500 times. What I want to do is create a loop in the code which calculates the 3 values for each excel row instead of using cards one by one. Here is the complete code that I used:

DATA Example;
INPUT tree dbh th k h dob;
b1=-3.6652; b2=1.7867; b3=-1.3066;
b4=25.0398; a1=0.8153; a2=0.1614;
y=(dob/dbh)**2; x=h/th; x1=x-1; x2=x*x-1 ;
* Matrix of Partial Derivatives;
 IF (x >= a1) THEN DO;
   zi1=x1; zi2=x2; zi3=0;
   zi4=0; zi5=0; zi6=0;
   END;
 ELSE DO; 
   IF (a2 <= x < a1) THEN DO;
     zi1=x1; zi2=x2; zi3=(a1-x)**2;
     zi4=0.0; zi5=2*b3*(a1-x);
     zi6=0.0;
     END ;
   ELSE DO ;
     zi1=x1; zi2=x2; zi3=(a1-x)**2;
     zi4=(a2-x)**2; zi5=2*b3*(a1-x);
     zi6=2*b4*(a2-x);
     END; 
 END;
* Vector of residuals; 
 IF x >= a1 THEN DO; 
   I1=0; I2=0; 
   END;
 ELSE DO ;
   IF a2 <= x < a1 THEN DO; 
     I1=1; I2=0;
     END;
   ELSE DO; 
     I1=1; I2=1;
     END;
 END;
est=b1*x1+b2*x2+b3*((a1-x)**2)*I1+b4*((a2-x)**2)*I2;
res=y-est;
* Variance (sd) and covariance (g) structures ;
 sd1=((dbh**-0.7525)*exp(-2.9874*x))**.5; 
CARDS;
2   40.0    19.4    1   7.8 29.0
;
RUN ;
PROC IML ;
USE Example ;
READ ALL VAR {zi1 zi2 zi3 zi4 zi5 zi6} INTO Zk;
READ ALL VAR {res} INTO RES;
READ ALL VAR {sd1} INTO Gk ;
D = {0.02847 0.04895 -0.5325, 0.04895 0.09752 -0.9661,
    -0.5325 -0.9661 338.22};
Rk = 0.02594 * Gk * Gk ;
Bk = {1 0 0 , 0 0 0, 0 1 0, 0 0 1, 0 0 0, 0 0 0};
Zk = Zk*Bk;
bk = D*Zk`*INV(Zk * D * Zk` + Rk)*RES;
PRINT bk;

I added the following lines to the beginning of the code in order to import my 6 variable rows as a data table.

PROC IMPORT OUT= WORK.dataset0 
            DATAFILE= "C:\Users\John\Desktop\calibration\aaa.xlsx" 
            DBMS=EXCEL REPLACE;
     SHEET="Sheet1"; 
     GETNAMES=YES;
     RUN;

And my datafile is like

tree dbh th k h dob
2 40.0 19.4 1 7.8 29.0
3 35.1 12.4 1 8.2 22.0
.  .     .  .  .    .
.  .     .  .  .    .

I need to replace the following lines to create a loop or something

CARDS;
2   40.0    19.4    1   7.8 29.0
;

in order to calculate the values for every each row.

I hope you can help! Thanks in advance.

I don't understand what the issue is. If you want a data step to read data from an existing dataset use a SET statement instead of INPUT statement. — Tom
SET statement worked for me. But the problem is IML statement should also be looped. Basically I don't want to copy and paste every excel (or datatable) row as a single card each time. This is time consuming. — Onur Alkan
You should be able to loop in the IML so it just reads one observation at a time instead of ALL observations. But why not just figure out how to make your IML work when there is more than one observations? So ZK is now an nX6 array instead of a 1X6 array. — Tom

Reeza Reeza · Accepted Answer · 2020-04-15T16:17:17

Does this not do what you want? At least for the first part, I didn't touch the IML portion.

data want;
set dataset0;

b1=-3.6652; b2=1.7867; b3=-1.3066;
b4=25.0398; a1=0.8153; a2=0.1614;
y=(dob/dbh)**2; x=h/th; x1=x-1; x2=x*x-1 ;
* Matrix of Partial Derivatives;
 IF (x >= a1) THEN DO;
   zi1=x1; zi2=x2; zi3=0;
   zi4=0; zi5=0; zi6=0;
   END;
 ELSE DO; 
   IF (a2 <= x < a1) THEN DO;
     zi1=x1; zi2=x2; zi3=(a1-x)**2;
     zi4=0.0; zi5=2*b3*(a1-x);
     zi6=0.0;
     END ;
   ELSE DO ;
     zi1=x1; zi2=x2; zi3=(a1-x)**2;
     zi4=(a2-x)**2; zi5=2*b3*(a1-x);
     zi6=2*b4*(a2-x);
     END; 
 END;
* Vector of residuals; 
 IF x >= a1 THEN DO; 
   I1=0; I2=0; 
   END;
 ELSE DO ;
   IF a2 <= x < a1 THEN DO; 
     I1=1; I2=0;
     END;
   ELSE DO; 
     I1=1; I2=1;
     END;
 END;
est=b1*x1+b2*x2+b3*((a1-x)**2)*I1+b4*((a2-x)**2)*I2;
res=y-est;
* Variance (sd) and covariance (g) structures ;
 sd1=((dbh**-0.7525)*exp(-2.9874*x))**.5; 

run;

How to use data rows instead of "cards" in a loop in SAS?

2 Answers