topic First and Last observations for Quarter and Account in New SAS User

First and Last observations for Quarter and Account

orangekitty — Mon, 06 Apr 2020 19:02:22 GMT

I have a data set that contains variables the following variables:

-account number (acctnum)

-date

-End of Quarter Date (i.e for 05/21/2018, EQD = 06/30/2018)

-Balance

I need to find the first and last value for balance for each end_of_quarter_date on an account by account basis.

I have tried doing a data step with several combinations using of first.variable/last.variable, but can't seem to get the program to output what I need. Any help would be greatly appreciated!

Re: First and Last observations for Quarter and Account

ballardw — Mon, 06 Apr 2020 19:12:47 GMT

Are your date and end of quarter date variables actually SAS date values or something else?

What do you want the output to look like?

It helps to provide example data in the form of a data step so we don't have to ask a lot of questions about your current data. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the <> icon or attached as text to show exactly what you have and that we can test code against.

If the values are actual SAS dates and hence would sort appropriately:

Proc sort data =have;

by account date;

run;

data want;

set have;

by account date endofquarter;

if first.endofquarter or last.endofquarter;

run;

would have a data set with two records per quarter (assuming there are two or more).

If that doesn't work you need to provide some example data and the desired output for the given data.

Re: First and Last observations for Quarter and Account

orangekitty — Mon, 06 Apr 2020 19:30:15 GMT

The output needs to be in the format of endofquarter_balance and beginningofquarter_balance for each account at a given quarterly level.

Re: First and Last observations for Quarter and Account

Tom — Mon, 06 Apr 2020 20:06:46 GMT

So each observations for a quarter has the same EDQ value?

data want;
  set have ;
  by accnum edq date ;
  if first.edq then opening_balance=balance ;
  retain opening_balance;
  if last.edq ;
  keep accnum edq opening_balance balance ;
  rename balance=closing_balance;
run;