topic Re: Date Mashing in SAS Enterprise Guide

Date Mashing

Verlea — Wed, 26 Jul 2017 14:34:17 GMT

I have effective and expiration dates in a dataset where I have transposed them so all for the key I have created are in a single row in the dataset. I want to be able to compare all of the dates to see if there is a break in the dates. My end result should be if I can combine the dates in the row to make one effective date and one expiration date. I can't use MIN(Eff_Dt) or MAX(Exp_Dt) because I might miss breaks in the dates.

I have attached a few examples in an excel spreadsheet.

Re: Date Mashing

RW9 — Wed, 26 Jul 2017 14:44:09 GMT

Hi,

Welcome to the community. First, I advise to post anything you need to, test data and code directly into the post, I wouldnt download Office files from the net as security risk.

From what you post, I question the need to transpose the data to find missing periods:

data want;
  set have;
  if current_date ne lag(current_date)+1 then flag="Y";
run;

So in this example, the lag lookks back one record and if its not 1 day more than the lag value sets a flag. No need to transpose to do this.