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
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;
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.
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
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;
I don't understand your last sentence.
What do you want your report to look like?
Ksharp
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: