I have the following dataset:
Account | Amount | Date | Paid |
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 |
I want the output below. How do I achieve this in SAS or using proc sql?
Account | Amount | Date | Paid |
1 | 200 | Jan-16 | 1 |
1 | 300 | Feb-16 | 0 |
1 | 600 | Mar-16 | 0 |
1 | 400 | Apr-16 | 0 |
2 | 600 | Feb-16 | 1 |
2 | 500 | Jan-16 | 0 |
2 | 300 | Mar-16 | 0 |
2 | 300 | Apr-16 | 1 |
3 | 600 | Mar-16 | 1 |
3 | 900 | Feb-16 | 0 |
3 | 200 | Jan-16 | 0 |
3 | 400 | Apr-16 | 1 |
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.
It would help if you explained what your sort order is in words, rather than us trying to guess it from your data.
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.
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;
Thank you Ksharp. This is exactly what I wanted!!
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.
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.
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.