Desktop productivity for business analysts and programmers

Getting the dataset in proper order

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Getting the dataset in proper order

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

 

 

 

 


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.

 

View solution in original post


All Replies
Super User
Posts: 4,026

Re: Getting the dataset in proper order

Posted in reply to sasuser0912

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

Posted in reply to sasuser0912

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!!

Respected Advisor
Posts: 4,797

Re: Getting the dataset in proper order

Posted in reply to sasuser0912

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

Need further help from the community? Please ask a new question.

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