Data with different time points

MagD — Fri, 12 Jun 2020 11:06:32 GMT

Hi All,

Please assist me. I am trying to get all variables only at 2 points, at the earliest date (LTPR1) and at the latest date (LTPR2). I have millions of records with different starting and end dates.

Current data:

Arrears	Balance	Instalemt	CD	Lifetime	Lifetime Period	LTPR1	LTPR12
-17	7400.61	323	0	A	201911	201910	202004
-391	6380.61	323	-1	A	202001	201910	202004
-340	7400.61	323	-1	A	201910	201910	202004
-68	6057.61	323	0	C	202003	201910	202004
-384	6710.61	323	-1	A	201912	201910	202004
-68	6380.61	323	0	D	202002	201910	202004
259.64	6062.25	323	1	8	202004	201910	202004

Desired output:

ID_Number	Arrears @1	Arrears @12	Balance @ 1	Balance @ 12	CD @ 1	CD @ 12	Lifetime @ 1	Lifetime @ 12	Lifetime Period @ 1	Lifetime Period @ 12	LTPR1	LTPR12
12345678910	-340	259.64	7400.61	6062.25	-1	1	A	8	201910	202004	201910	202004

This is the code I currently have:

proc sql;
create table Sub2
as select *, min('Lifetime Payment Rating Finmonth'n) as LTPR1, max('Lifetime Payment Rating Finmonth'n) AS LTPR12
from Variables
where Sub = 2 and Income_Doc_Type is not null
group by ID_Number
having Nett_Monthly_Income = max(Nett_Monthly_Income)
and ID_Number=max(ID_Number);
run;

Re: Data with different time points

RichardDeVen — Fri, 12 Jun 2020 11:38:02 GMT

You can self merge where selected rows by ID, renaming the columns as desired.

Example:

data have;
id = '123456';
input 
Arrears Balance Instalemt CD Lifetime $ Lifetime_Period LTPR1    LTPR12;
datalines;
-17    7400.61    323    0    A    201911    201910    202004
-391   6380.61    323   -1    A    202001    201910    202004
-340   7400.61    323   -1    A    201910    201910    202004
-68    6057.61    323    0    C    202003    201910    202004
-384   6710.61    323   -1    A    201912    201910    202004
-68    6380.61    323    0    D    202002    201910    202004
259.64 6062.25    323    1    8    202004    201910    202004
;


data want;
  attrib
    id         length = $10
    arrears1   label = "Arrears @1"
    arrears12  label = "Arrears @12"
    balance1   label = "Balance @1"
    balance12  label = "Balance @12"
    cd1        label = "CD @1"
    cd12       label = "CD @12"
    lifetime1  label = "Lifetime @1"
    lifetime12 label = "Lifetime @12"
    lifetime_period1  label = "Lifetime Period @1"
    lifetime_period12 label = "Lifetime Period @12"
  ;
  merge
    have (where=(lifetime_period1=ltpr1)
      rename = (
        arrears   = arrears1
        balance   = balance1
        cd        = cd1
        lifetime  = lifetime1
        lifetime_period = lifetime_period1
      )
      drop = instalemt
    )
    have (where=(lifetime_period12=ltpr12)
      rename = (
        arrears   = arrears12
        balance   = balance12
        cd        = cd12
        lifetime  = lifetime12
        lifetime_period = lifetime_period12
      )
      drop = instalemt
    )
  ;
  by id ltpr1 ltpr12;
run;

topic Re: Data with different time points in SAS Programming

Data with different time points

Re: Data with different time points