Suggestion on grouping

[yash82]

Hi there,

I need to group my data based on condition of (current year -10 and below) in one group and remaining all in other group in my dataset. What should be possible chances apart from proc format.

Data new.

input year price1 price2 price3

1999 100 200 300

2000 200 300 400

1996 500 400 800

2002 7000 9000 1200

1992 6000 2000 10000

run,

So I want data like this

Year price1 price2 price3

1992 6000 2000 10000

1996 500 400 800

1999 100 200 300

2000 200 300 400

2002 7000 9000 1200

So what should be my best bet .. here we have current year set as 2011.... I do not want to display groups as something else... I want it to be printed out as it is... but just reordered... Any suggestion is highly appreciated

[Peter_C]

proc sort?

[art297 · Accepted solution]

I agree with Peter regarding including a proc sort, but you may also want to use if then logic or, similarly, the ifn function.  E.g.,

data have;

  input year price1 price2 price3;

  cards;

1999 100 200 300

2000 200 300 400

1996 500 400 800

2002 7000 9000 1200

1992 6000 2000 10000

;

proc sort data=have;

  by year;

run;

data want;

  set have;

  group=ifn(year le year(today())-10,1,2);

run;

proc print data=want;

  by group;

run;

[yash82]

Wow... thanks guys...I never thought about ifn function though...

[MikeZdeb]

hi ... you could also use Art's suggestion in one step ...

proc sql;

create table want as

select *, (year gt year(today())-10) as group from have

order by group, year;

quit;

[art297]

Actually, since you are only trying to identify two groups, I like Mike's suggestion of calculating a boolean better than using ifn.  And, that can be done in either a datastep or proc sql.

However, if you decide to use proc sql, I'd remove group from the order by clause, as it is already accounted for by year.  Interestingly, such a small change, improves performance by almost 50%:

proc sql;

  create table want as

    select *,

      year gt year(today())-10 as group

         from have

           order by year

  ;

quit;