topic Re: Getting the dataset in proper order in SAS Enterprise Guide

Getting the dataset in proper order

sasuser0912 — Sat, 21 Jul 2018 08:52:01 GMT

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

Re: Getting the dataset in proper order

SASKiwi — Sat, 21 Jul 2018 09:30:50 GMT

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

Re: Getting the dataset in proper order

sasuser0912 — Sat, 21 Jul 2018 10:32:26 GMT

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.

Re: Getting the dataset in proper order

Ksharp — Sat, 21 Jul 2018 11:10:04 GMT

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;

Re: Getting the dataset in proper order

sasuser0912 — Sat, 21 Jul 2018 14:57:58 GMT

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

Re: Getting the dataset in proper order

Patrick — Sun, 22 Jul 2018 11:25:37 GMT

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