topic Re: Sorting data in SAS Programming

Sorting data

Hhh111 — Tue, 09 Apr 2013 16:44:06 GMT

all sas expert, pls help me..

pls view attached files for clearer view..my objective is to gain latest date..pls be informed that there are 2 code for each account..

code 780 - start date

code 660 - end date

i need to extract data from code 780 to code 660

for picture a, when we sort by date, it turned out that the sequence start from 2 code for 780 and 1 code from 660.

when i sort nodupkey descending by code and date, i manage to get latest date for each code ie.

code 780 - 20/11/2004

code 660 - 6/8/2009

for picture b, when we sort by date, seems that the code is mix between code 780 and code 660..

if i sort nodupkey descending by code and date, my final date for code 780 is 1/12/2010..

However, My user insist if there is code 660 in between code 780, i need to choose the first code 780 which is 1/5/2009

not the latest date...

what the code i should use that can cater for both cases..no code 660 in between and code 660 in between..

to all sas programmer, pls help me to solve this issue..thank you!!

Re: Sorting data

PGStats — Tue, 09 Apr 2013 17:34:49 GMT

Things would be easier to understand with an example. Input (account, code, date) and expected output (account, ??). Use text, not pictures, please. - PG

Re: Sorting data

Astounding — Tue, 09 Apr 2013 17:35:00 GMT

What would you like the data to look like when you are done?

Here are examples of possible answers.

Keep a subset of the rows, keeping only the rows that begin and end a "picture".

Create one row per "picture" holding both a starting date and an ending date.

These are not the only possible answers, and you can come up with any answer that you would like. It might help if you specify the names of a few additional variables and what you would like done with those.

Re: Sorting data

Hhh111 — Wed, 10 Apr 2013 00:42:31 GMT

Thanks for ur reply PGStats..

account : a (after sorting by date)

code. Date Code. Date

780. 20/3/2004. --------->. Expected output. 780 (start date). 20/11/2004

780. 20/11/2004. 660 (end date). 6/8/2009

660. 6/8/2009

After i sort nodupkey by descending date, the expected output is as above..i take the latest date for 780 code

Account : b (after sorting by date)

code. Date Code. Date

780. 20/3/2004. --------->. Expected output. 780 (start date). 20/3/2004

660. 20/11/2004. 660 (end date). 10/10/2010

780. 6/8/2008

660. 10/10/2010

The end date, i still pull the most latest one..but for 780 code, i cannot get the latest date (20/11/2004) because there are 660 in between 2 code of 780..my user insist if the code goes by that sequence, i need to take the oldest date for code 780 only..code 660 remain unchanged, means that still take the latest one..

i Hope u get the better picture and help me in solving this...tq

Re: Sorting data

Reeza — Wed, 10 Apr 2013 02:05:12 GMT

Your description of what you want and data doesn't seem to match. It sounds like you need a max/min function, not a sort, but you use the earliest 780 code rather than the latest.

Did you post what you have versus what you want?

Try proc means or summary with code as your class and look for the max or min. You may need to reformat the data output.

Re: Sorting data

PGStats — Wed, 10 Apr 2013 02:14:22 GMT

Here is what I would propose (asuming there are only two possible codes) :

data test;
input account $ code date :ddmmyy.;
format date ddmmyys10.;
datalines;
a 780.      20/03/2004
a 780.      20/11/2004
a 660.      06/08/2009
b 780.      20/03/2004
b 660.      20/11/2004
b 780.      05/08/2008
b 780.      06/08/2008
b 660.      10/10/2010
;

proc sort data=test; by account descending date ; run;

data want(keep=account startDate endDate);
do until(last.account);
     set test; by account;
     if code=660 then
          if missing(endDate) then endDate = date;
          else oldEndDate = date;
     else if not missing(endDate) and missing(oldEndDate) then
           startDate = date;
     end;
output;
format startDate endDate oldEndDate ddmmyys10.;
run;

proc print data=want; run;

Seems to work... Try it with more cases.

Re: Sorting data

Hhh111 — Wed, 10 Apr 2013 05:44:16 GMT

Dear PG,

if i use that code,i'll get the latest date for each code

my concern is for account = b..because after sort descending by date,

the code=660 appear in between 2 code of 780..

if such cases, the date for 780 that i should extract is the old date..not the latest date

different from account=a because the code=660 seems to appear at the bottom

Re: Sorting data

PGStats — Wed, 10 Apr 2013 14:26:20 GMT

Have you tested the code? Please show tests, results and desired output. - PG