topic Suggestion on grouping in SAS Programming

Suggestion on grouping

yash82 — Sun, 11 Dec 2011 08:33:23 GMT

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

Suggestion on grouping

Peter_C — Sun, 11 Dec 2011 14:54:44 GMT

proc sort?

Suggestion on grouping

art297 — Sun, 11 Dec 2011 14:59:17 GMT

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;

Suggestion on grouping

yash82 — Sun, 11 Dec 2011 15:17:48 GMT

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

Re: Suggestion on grouping

MikeZdeb — Sun, 11 Dec 2011 17:37:15 GMT

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;

Re: Suggestion on grouping

art297 — Sun, 11 Dec 2011 22:38:19 GMT

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;