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

I have the following dataset:

AccountAmountDatePaid
1200Jan-161
1300Feb-160
1600Mar-160
1400Apr-160
2500Jan-160
2600Feb-161
2300Mar-160
2300Apr-161
3200Jan-160
3900Feb-160
3600Mar-161
3400Apr-161

I want the output below. How do I achieve this in SAS or using proc sql?

AccountAmountDatePaid
1200Jan-161
1300Feb-160
1600Mar-160
1400Apr-160
2600Feb-161
2500Jan-160
2300Mar-160
2300Apr-161
3600Mar-161
3900Feb-160
3200Jan-160
3400Apr-161

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
sasuser0912
Fluorite | Level 6

Sorry for not making it clear what I am trying to achieve. The dataset contains Amount by account at a particular date and also if the amount was paid or not. Paid=1 refers to paid and paid=0 refers to not paid.

 

I am trying to get the values of the balance at account level when paid =1. For example for account 2 I want paid=1 to be the first entry with the corresponding amount=600 and date=Feb16.

 

So far I have tried this:

 

data test1;

set test;

if first.account then do;

   if Paid=1 then

    balance=new_balance ;

     dete=new_date;

end;

run;

 

This however does not seem to work.

 

View solution in original post

5 REPLIES 5
SASKiwi
PROC Star

It would help if you explained what your sort order is in words, rather than us trying to guess it from your data.

sasuser0912
Fluorite | Level 6

Sorry for not making it clear what I am trying to achieve. The dataset contains Amount by account at a particular date and also if the amount was paid or not. Paid=1 refers to paid and paid=0 refers to not paid.

 

I am trying to get the values of the balance at account level when paid =1. For example for account 2 I want paid=1 to be the first entry with the corresponding amount=600 and date=Feb16.

 

So far I have tried this:

 

data test1;

set test;

if first.account then do;

   if Paid=1 then

    balance=new_balance ;

     dete=new_date;

end;

run;

 

This however does not seem to work.

 

Ksharp
Super User

You want this ?

 

data have;
infile cards expandtabs truncover;
input Account	Amount	Date monyy7.	Paid;
format date monyy7.;
cards;
1	200	Jan-16	1
1	300	Feb-16	0
1	600	Mar-16	0
1	400	Apr-16	0
2	500	Jan-16	0
2	600	Feb-16	1
2	300	Mar-16	0
2	300	Apr-16	1
3	200	Jan-16	0
3	900	Feb-16	0
3	600	Mar-16	1
3	400	Apr-16	1
;
run;
data temp;
 set have;
 by account;
 retain group;
 if first.account then group=0;
 if account=lag(account) and lag(paid)=1 then group=1;
 if group=0 then _date=-date;
  else _date=date;
run;
proc sort data=temp out=want;
by account group  _date;
run;
sasuser0912
Fluorite | Level 6

Thank you Ksharp. This is exactly what I wanted!!

Patrick
Opal | Level 21

@sasuser0912

Then please mark @Ksharp's answer as the solution to A) give him the credit he deserves and B) your question gets flagged as resolved.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 796 views
  • 1 like
  • 4 in conversation