## Getting the dataset in proper order

Solved
Occasional Contributor
Posts: 9

# Getting the dataset in proper order

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

Accepted Solutions
Solution
a month ago
Occasional Contributor
Posts: 9

## Re: Getting the dataset in proper order

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.

All Replies
Super User
Posts: 4,026

## Re: Getting the dataset in proper order

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

Solution
a month ago
Occasional Contributor
Posts: 9

## Re: Getting the dataset in proper order

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.

Super User
Posts: 10,850

## Re: Getting the dataset in proper order

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;``````
Occasional Contributor
Posts: 9

## Re: Getting the dataset in proper order

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

Posts: 4,797

## Re: Getting the dataset in proper order

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

☑ This topic is solved.