Create table containing the 4 most recent items 1

[Katie6]

Hi there,

I have a table containing News items grouped into different categories, like so:

[b]Category      Story                              Date[/b]
----------------------------------------------------------
Sport         Team wins match                    14Oct2007 
Sport         Team does not win match            15Oct2007 
Sport         Football results                   16Oct2007 
Sport         Hockey player falls over           01Nov2007 
Sport         Hooligans spoil match              12Sep2007 
Buiness       Bank collapses                     14Oct2007 
Buiness       Company boss gets the sack         24Oct2007 
Buiness       FTSE 100                           29Oct2007 
Buiness       Manager found under the table      30Oct2007 
Buiness       Tramp inherits company             19Nov2007 
Politics      Prime Minister trips over          15Oct2007 
Politics      President visits North Pole        18Oct2007

From this table, I would like to create another table containing the 4 most recent stories in each category, where story1 is the most recent. It would look like this:

[b]Category    Story1             Story2         .  Story3          Story4 [/b]
---------------------------------------------------------
Sport       Hockey player...   Football results  Team does...   Team wins...
Buiness     Tramp inherits..   Manager found...  FTSE 100  .    Company b...
Politics    President visits.. Prime Minister..

Is there a clever way to do this (or even a not-so-clever way!)?

Many thanks,

Katie

 

[kdt82]

Heya, proc transpose is the answer.

proc sort data=sports;
by category date;
run;

data top4(drop=count date);
   set sports;
   by category;
   if first.category then count=0 ;
   else count+1;
   if count<4;
   run;

proc transpose data=top4 out=test(drop=_name_) prefix=story;
   by category;
   var story;
   run;

proc print data=test;
run;

 

[Katie6]

I haven't tried this code yet, but it looks very clever!

Thank you