SAS proc sql do loop

Question

I am currently working on a credit card delinquency project. need help from expert to see if this can happen

so I have this code

data mcmc2;
set work.mcmc;
array delq(1:15) $2. delq_1-delq_15;

do i = 1 to 15;
delq(i) = substr(delq_36_rev, (i),2) ;
put @1 delq(i);
end;

run;

this code generate 15 variable from delq_36_rev

delq_36_rev looks like xxxxxxxxxxxxxxx (where 0<=x<=7) so delq_i looks like xx

thing I want to do starts from here.

from delq_1 ~ delq_15 I would give score based on numbers

(eg. 01 = 1 point, 12 = 2 points, 23= 3 points)

but I would also combine scores altogether

so I would like to write a do loop like

do i = 1 to 15
         when delq_i ="70" then score_i=-6 
         when delq_i in ("71","60") then score_i=-5 
         when delq_i in ("72","61","50") then score_i=-4
         when delq_i in ("73","62","51","40") then score_i=-3
         when delq_i in ("74","63","52","41","30") then score_i=-2
         when delq_i in ("76","65","65","64","54","53","43","42","32","31","20","21","10") then score_i=-1
         when delq_i ="00" then score_i=0
         when delq_i in ("01","11","22","33","44","55","66","77" then score_i=1      
         when delq_i ="12" then score_i=2 
         when delq_i ="23" then score_i=3
         when delq_i ="34" then score_i=4 
         when delq_i ="45" then score_i=5
         when delq_i ="56" then score_i=6 
         when delq_i ="67" then score_i=7

 sum(delq_1-delq_15) as delq_score

please help!!

user667489 · Accepted Answer · 2016-10-15 21:58:30Z

1

If I understand you correctly, you can do all of this easily in one data step, without resorting to macros:

data want;
  set set work.mcmc;
  array delq(15) $2. delq_1-delq_15;
  array score(15);
  do i = 1 to dim(delq);
    delq[i] = substr(delq_36_rev,i,2);
    select(delq[i]);
      when("70") score[i]=-6;
      when("71","60") score[i]=-5;
      /*etc*/
      otherwise call missing(score[i]);
    end;
  end;
  delq_score = sum(of score[*]);
run;

answered Oct 15, 2016 at 21:58

user667489

9,5772 gold badges28 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Parfait · Accepted Answer · 2016-10-15 21:15:14Z

Consider using an SQL update statement with CASE logic within a macro. But first add score_1-score_15columns along with your delq columns. Then in macro, update these score columns iteratively and in one last data step add them all up into score, finally dropping the 15 score items.

data mcm2;
    set work.mcmc;
    array delq(1:15) $2. delq_1-delq_15;
    array score(1:15) score_1-score_15;

    do i = 1 to 15;
        delq(i) = substr(delq_36_rev, (i),2) ;     * delq_1-delq_15;
        put @1 delq(i);
        score(i) = .;                              * score_1-score_15;
    end;
run;


%macro loopSQL;
    %do i = 1 %to 15;

        proc sql;
            UPDATE mcm2
            SET score_&i = 
             CASE 
                WHEN delq_&i ="70" THEN -6 
                WHEN delq_&i in ("71","60") THEN -5 
                WHEN delq_&i in ("72","61","50") THEN -4
                WHEN delq_&i in ("73","62","51","40") THEN -3
                WHEN delq_&i in ("74","63","52","41","30") THEN -2
                WHEN delq_&i in ("76","65","65","64","54","53","43","42","32","31","20","21","10") THEN -1
                WHEN delq_&i = "00" THEN 0
                WHEN delq_&i in ("01","11","22","33","44","55","66","77") THEN 1      
                WHEN delq_&i ="12" THEN 2 
                WHEN delq_&i ="23" THEN 3
                WHEN delq_&i ="34" THEN 4 
                WHEN delq_&i ="45" THEN 5
                WHEN delq_&i ="56" THEN 6 
                WHEN delq_&i ="67" THEN 7 
                ELSE .
            END;
        quit;

    %end;
%mend loopSQL;

%loopSQL;                                          * run macro;


data work.mcm2;
    set work.mcm2;

    score = sum(of score_1-score_15);   
    drop score_1-score_15;
run;

Collectives™ on Stack Overflow

SAS proc sql do loop

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related