Create new Varaibles by number of months since loan month

[Ronein]

Hello

I have the following row data tables:

1-Loans table that has information about loans that customer took during year 2019.

2-Information about score in each month for each customer.

Then I merge the tables and get a table that contain information of  loans and scores during 2019.

My wanted table will contain information of scores by number of months passed from month_loan.

For example:

For customer ID=1 and loan=17OCT2019 there will be : Score1=7  Score2=7  and Score3 until Score11 will have null value

For customer ID=1 and loan=23NOV2019 there will be : Score1=7   and Score2 until Score11 will have null value

 For customer ID=4 and loan=19MAY2019 there will be : Score1=7   Score2=7  Score3=7  Score4=7 Score5=8  Score6=8  Score7=8 and Score9-Score11 will have null value

%include '!RSMEHOME/SASCode/SHARECode/Libname.sas';	

Data Loans;
Retain ID date_Loan Month_Loan Sum_Loan ; 
informat date_Loan date9.;
format date_Loan date9.;
input ID date_Loan Sum_Loan;
Month_Loan=put(date_Loan,yymmn4.);
cards;
1 '17OCT2019'd 1000
2 '03JAN2019'd 900
1 '23NOV2019'd 3000
3 '22JUN2019'd 1500
3 '05MAR2019'd 300
4 '19May2019'd 700
;
run;


Data Score1901;
input ID month Score;
cards;
2 1901 5
3 1901 2
1 1901 8
4 1901 8
;
Run;


Data Score1902;
input ID month Score;
cards;
4 1902 8
1 1902 7
2 1902 5
3 1902 3
;
Run;

Data Score1903;
input ID month Score;
cards;
3 1903 2
1 1903 7
2 1903 4
4 1903 8
;
Run;


Data Score1904;
input ID month Score;
cards;
1 1904 6
2 1904 5
3 1904 2
4 1904 8
;
Run;

Data Score1905;
input ID month Score;
cards;
1 1905 6
2 1905 5
3 1905 3
4 1905 7
;
Run;

Data Score1906;
input ID month Score;
cards;
2 1906 4
1 1906 6
3 1906 5
4 1906 7
;
Run;

Data Score1907;
input ID month Score;
cards;
1 1907 8
2 1907 3
3 1907 5
4 1907 7
;
Run;

Data Score1908;
input ID month Score;
cards;
1 1908 8
2 1908 5
3 1908 5
4 1908 7
;
Run;

Data Score1909;
input ID month Score;
cards;
1 1909 7
2 1909 4
3 1909 6
4 1909 7
;
Run;


Data Score1910;
input ID month Score;
cards;
1 1910 9
2 1910 4
3 1910 6
4 1910 8
;
Run;

Data Score1911;
input ID month Score;
cards;
1 1911 7
2 1911 4
3 1911 6
4 1911 8
;
Run;


Data Score1912;
input ID month Score;
cards;
4 1912 8
1 1912 7
2 1912 2
3 1912 9
;
Run;


%let vector=1901+1902+1903+1904+1905+1906+1907+1908+1909+1910+1911+1912;
%let n=12;/*Number of arguments in &vector.*/
%macro mmacro1;
%DO i=1  %TO  &n.;
%let YYMM=%scan(&vector.,&i.,+);
proc sort data=Score&YYMM.;
By ID;
Run;
%end;
%mend mmacro1; 
%mmacro1; 

proc sort data=Loans;by ID;Run;
%macro mmerge;
%DO i=1  %TO  &n.;
%let YYMM=%scan(&vector.,&i.,+);
Score&YYMM.(keep=Id score rename=(Score=Score&YYMM.))
%end;
%mend mmerge; 
%mmacro1; 


Data tbl1;
Merge Loans %mmerge;
by ID;
Run;


 

[andreas_lds]

Maybe i am to short-sighted, but what's the question?

[Ronein]

The question is how to create "wanted" data set as you see in the photo?

I have also explained the logic that I need to have information about scores from 1 month after the month that loan was taken .

[Patrick]

Here you go.

data scores;
  set score19:;
  format month_loan yymmn4.;
  month_loan=input(put(month,z4.),yymmn4.);
run;

proc sql;
  create table inter as
  select 
    l.Sum_Loan as Sum_Loan, 
    s.Month_Loan as Month_Loan, 
    l.Date_Loan as Date_Loan, 
    l.id as ID, 
    s.score 
  from loans l left join scores s
  on l.id=s.id and l.date_loan<s.month_loan
  order by l.id, l.date_loan, s.month_loan
  ;
quit;

data want(drop=_: score);
  array Score_ {11} 8. Score_11 - Score_1;
  retain Score_;
  set inter;
  by id date_Loan;
  _n+1;
  Score_[12-_n]=score;
  if last.date_loan then
    do;
      output;
      call missing(_n, of Score_[*]);
    end;
run;

proc print data=want;
run;