topic Re: proc sort with form of Pre 1995, 1995, 1996.. in SAS Procedures

proc sort with form of Pre 1995, 1995, 1996..

Fred_Gavin — Fri, 08 Jan 2010 16:21:32 GMT

I have the data with years defined as pre 1995, 1995, 1996 and so on.

the sort procedure gives the results sorting single year first, and put pre 1995 at last.

How should I make the sorting procedure as defined ?

Pre 1995
1995
1996
...

Many Thanks

Re: proc sort with form of Pre 1995, 1995, 1996..

Sandhya — Fri, 08 Jan 2010 16:32:17 GMT

One of the solution is

proc format;
value $yr '10'='pre 1995';
run;

data test;
set test;
if year='pre 1995' then year = '10';
format year yr.;
run;

Sandy.

Re: proc sort with form of Pre 1995, 1995, 1996..

Fred_Gavin — Fri, 08 Jan 2010 16:58:07 GMT

Thanks for the reply.

Your way is actually the same as define those in the data step.
So in the dataset they still appear to be pre 1995,
1996, 1997......

When you sort those, it returns
1996
1997
1998
.....
pre 1995.

But I need it to be
pre 1995
1996
1997
.....

Re: proc sort with form of Pre 1995, 1995, 1996..

Sandhya — Fri, 08 Jan 2010 17:08:20 GMT

Hi,

See what is the first value of the sorted order. Give a value lesser that that instead of 10.

Regards,
Sandy.

Re: proc sort with form of Pre 1995, 1995, 1996..

Fred_Gavin — Fri, 08 Jan 2010 17:32:46 GMT

Oh, it works now.

Thank you very much.

Re: proc sort with form of Pre 1995, 1995, 1996..

DanielSantos — Fri, 08 Jan 2010 18:12:11 GMT

Yet another way of performing a sort by formatted values, would be:
[pre]
* create format;
data _FORMAT;
set INDATA (rename=(YEAR=START));
keep FMTNAME TYPE HLO START END LABEL;
retain FMTNAME 'YR'
TYPE 'C'
HLO '';
END=START;
LABEL=put(input(START,??best.),best.); * convert value to numeric;
run;
proc format cntlin=_FORMAT;
run ;

* fomatted sort;
proc sql;
create table OUTDATA as
select * from INDATA order by put(YEAR,$YR.);
quit;
[/pre]
With this any non pure digit value of YEAR will be sorted as the lowest value (hence "pre 1995").

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

Re: proc sort with form of Pre 1995, 1995, 1996..

Peter_C — Sat, 09 Jan 2010 11:52:38 GMT

'guess a choice of preferred method depends on more than we know. I have often had to group information into non-equal time bands (like pre1995, 1995-2000, 2001,2002,2003, ... lastyear, months of this year).
With that requirement, I found date-type class variables in the reporting procedures needed no alteration, just to be formatted with a simple user format, like:[pre]
proc format ;
value clDate
low - '31dec1994'd = 'pre- 1995'
'1jan1995'd - '31dec2000'd = '1995-2000'
'1jan2001'd - "%sysfunc( intnx( year, "&sysdate"d, -1,e ), date9)"d =[year4.]
"%sysfunc( intnx( year, "&sysdate"d, 0,b ), date9)"d - high = [monyy7.]
run ;[/pre]Then given a sas data set with tran_date, I can get stats in this collection of un-equal time bands with something like[pre]proc tabulate data= my_tran_data missing ;
class tran_date ;
format tran_date clDate. ;
var purchase sale actual forecast ;
table ( tran_date all), (purchase sale actual forecast)
*sum=' '*f= comma11./ rts=15 ;
run ;[/pre]
use variables that are more relevant to you

PeterC
[beware untested code]