04-09-2013 12:44 PM
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!!
04-09-2013 08:42 PM
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
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
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
04-09-2013 10:05 PM
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.
04-09-2013 10:14 PM
Here is what I would propose (asuming there are only two possible codes) :
input account $ code date :ddmmyy.;
format date ddmmyys10.;
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);
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;
format startDate endDate oldEndDate ddmmyys10.;
proc print data=want; run;
Seems to work... Try it with more cases.
04-10-2013 01:44 AM
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
04-09-2013 01:35 PM
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.