Help using Base SAS procedures

sorting one group and ignoring the other

Accepted Solution Solved
Reply
Regular Contributor
Posts: 196
Accepted Solution

sorting one group and ignoring the other

How do I sort this dataset by 2010 and ignore 2009?
I created a proc report where year is an "across" variable and item is the group. I would like the items to be sorted based on 2010.

year item count
2009 a     10
2009 b     30
2009 c     5
2010 a     20

2010 b     15
2010 c     3


Accepted Solutions
Solution
‎11-10-2011 02:09 AM
SAS Super FREQ
Posts: 8,743

Re: sorting one group and ignoring the other

Hi:

  I'm confused by your data and your description. Without any other information from you, PROC REPORT will order the ROWS, based on the values of the ITEM variable. So the default order for your ROWS would be A first, then B, then C.

  Do you want the ROWS to be reordered based on the values for the same items in 2010???? If so, it is possible that the code below might do what you want. I changed the data for 2010, so that the final order would be something different from the PROC REPORT default of A, B, C.

cynthia

                

** make some data;
** changed 2010 so that count for item C is largest;
** because PROC REPORT default order for item;
** would be A, B, C and I wanted the final order;
** to be different from the default.;
data testyear(keep=year item count)
     ordyr(keep=item ordyr);
  infile datalines;
  input year item $ count;
  output testyear;
  if year = 2010 then do;
     ordyr = count;
     output ordyr;
  end;
return;
datalines;
2009 a     10
2009 b     30
2009 c     5
2010 a     12
2010 b      5
2010 c     20
2007 a     3
2007 b     4
2007 c     5
2008 a     1
2008 b     3
2008 c     2
;
run;

                 

** Join the two tables, so that;
** ORDYR from 2010 is joined to every year;
proc sql;
  create table final as
  select a.ordyr, b.item, b.year, b.count
  from ordyr as a,
       testyear as b
  where a.item = b.item;
quit;

                 

** Show the PROC REPORT default, versus using the ORDYR variable;
** for ordering the rows;
ods html file='c:\temp\testrept.html' style=sasweb;
proc report data=final nowd;
  title '1) proc report default';
  column item year,count;
  define item /group;
  define year / across;
  define count / sum;
run;
                        
proc report data=final nowd;
  title '2) using ordyr variable';
  column ordyr item year,count;
  define ordyr / group descending /* noprint */;
  define item /group;
  define year / across;
  define count / sum;
run;
ods html close;

View solution in original post


All Replies
Valued Guide
Posts: 2,175

sorting one group and ignoring the other

is it a where filter you need?

It can be a statement

        where year NE 2009 ;

or a data set option

        data= your_data( where=( year NE 2009 ))

your choice.

Super User
Posts: 5,257

sorting one group and ignoring the other

No, I don't think so - year is across.

I'm not much of a proc report guy, it could probably solved there.

But you could on beforehand extract 2010 data, find out your order (a rank on count in your case?).

Then add an ordering variable (_n_ in data step will do). And join back on your original data on item column.

And then, you use the new variable as a non display column, used for ordering only.

Linus

Data never sleeps
Solution
‎11-10-2011 02:09 AM
SAS Super FREQ
Posts: 8,743

Re: sorting one group and ignoring the other

Hi:

  I'm confused by your data and your description. Without any other information from you, PROC REPORT will order the ROWS, based on the values of the ITEM variable. So the default order for your ROWS would be A first, then B, then C.

  Do you want the ROWS to be reordered based on the values for the same items in 2010???? If so, it is possible that the code below might do what you want. I changed the data for 2010, so that the final order would be something different from the PROC REPORT default of A, B, C.

cynthia

                

** make some data;
** changed 2010 so that count for item C is largest;
** because PROC REPORT default order for item;
** would be A, B, C and I wanted the final order;
** to be different from the default.;
data testyear(keep=year item count)
     ordyr(keep=item ordyr);
  infile datalines;
  input year item $ count;
  output testyear;
  if year = 2010 then do;
     ordyr = count;
     output ordyr;
  end;
return;
datalines;
2009 a     10
2009 b     30
2009 c     5
2010 a     12
2010 b      5
2010 c     20
2007 a     3
2007 b     4
2007 c     5
2008 a     1
2008 b     3
2008 c     2
;
run;

                 

** Join the two tables, so that;
** ORDYR from 2010 is joined to every year;
proc sql;
  create table final as
  select a.ordyr, b.item, b.year, b.count
  from ordyr as a,
       testyear as b
  where a.item = b.item;
quit;

                 

** Show the PROC REPORT default, versus using the ORDYR variable;
** for ordering the rows;
ods html file='c:\temp\testrept.html' style=sasweb;
proc report data=final nowd;
  title '1) proc report default';
  column item year,count;
  define item /group;
  define year / across;
  define count / sum;
run;
                        
proc report data=final nowd;
  title '2) using ordyr variable';
  column ordyr item year,count;
  define ordyr / group descending /* noprint */;
  define item /group;
  define year / across;
  define count / sum;
run;
ods html close;

Super User
Posts: 9,682

sorting one group and ignoring the other

I don't understand your last sentence.

What do you want your report to look like?

Ksharp

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 118 views
  • 0 likes
  • 5 in conversation