BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
orangekitty
Fluorite | Level 6

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! 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

3 REPLIES 3
ballardw
Super User

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-dat... 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.

orangekitty
Fluorite | Level 6
The output needs to be in the format of endofquarter_balance and beginningofquarter_balance for each account at a given quarterly level.
Tom
Super User Tom
Super User

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;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 941 views
  • 2 likes
  • 3 in conversation