DATA Step, Macro, Functions and more

Suggestion on grouping

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

Suggestion on grouping

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


Accepted Solutions
Solution
‎12-11-2011 09:59 AM
PROC Star
Posts: 7,471

Suggestion on grouping

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;

View solution in original post


All Replies
Valued Guide
Posts: 2,177

Suggestion on grouping

proc sort?

Solution
‎12-11-2011 09:59 AM
PROC Star
Posts: 7,471

Suggestion on grouping

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;

Contributor
Posts: 25

Suggestion on grouping

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

Valued Guide
Posts: 765

Re: Suggestion on grouping

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;

PROC Star
Posts: 7,471

Re: Suggestion on grouping

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 174 views
  • 1 like
  • 4 in conversation