CODEWITHSUNDEEP
sastime-series
py14ak
py14ak

Reputation: 31

Finding the max value of a variable in SAS per ID per time period

 proc sql;
create table abc as select distinct formatted_date ,Contract, late_days
from merged_dpd_raw_2602
group by 1,2
;quit;

this gives me the 3 variables I\m working with

they have the form

|ID|Date in YYMMs.10| number|

proc sql;
create table max_dpd_per_contract as select distinct contract, max(late_days) as DPD_for_contract
from sasa
group by 1
;quit;

this gives me the maximum number for the entire period but how do I go on to make it per period?

I'm guessing the timeseries procedure should be used here.

proc timeseries data=sasa
                out=sasa2;
by contract;
id formatted_date interval=day ACCUMULATE=maximum ;
trend maximum ;

var late_days;

run;

but I am unsure how to continue.

I want to to find the maximum value of the variable "late days" per a given time period(month). So for contact A for the time period jan2018 the max late_days value is X.

how the data looks:https://i.sstatic.net/Ee3MZ.jpg

Upvotes: 1

Views: 1529

Answers (1)

Richard
Richard

Reputation: 27508

In SQL you will want to calculate your aggregate within a group that uses a computed month value.

Example:

data have;
  call streaminit(2021);

  length contract date days_late 8;

  do contract = 1 to 10;
    days_late = 0;
    do date = '01jan2020'd to '31dec2020'd;
      if days_late then 
        if rand('uniform') < .55 then 
          days_late + 1;
        else
          days_late = 0;
      else
        days_late + rand('uniform') < 0.25;

      output;
    end;
  end;
  format date date9.;
run;

options fmterr;

proc sql;
  create table want as 
  select 
    contract
  , intnx('month', date, 0) as month format = monyy7.
  , max(days_late) as max_days_late
  from
    have
  group by
    contract, month
  ;

You will get the same results using Proc MEANS

proc means nway data=have noprint; 
  class contract date;
  format date monyy7.;
  output out=want_2 max(days_late) = max_days_late;
run;

Upvotes: 1

Related Questions