BookmarkSubscribeRSS Feed
efunk
Calcite | Level 5
There are two countries, around 50 regions and a lot of different items in my dataset.
What I want to do?
Leave in table only rows with top 3 "sales" for each country, each item and week. In other words, if I have data like this:
Country Region Item Week Sales
LV r1 i1 1 12
LV r2 i1 1 11
LV r3 i1 1 10
LV r4 i1 1 15
LV r5 i1 1 13
......
LT r6 i1 1 12
LT r7 i1 1 10
LT r8 i1 1 11
LT r9 i1 1 18
LT r0 i1 1 20

I want to get such results:
Country Region Item Week Sales
LV r1 i1 1 12
LV r4 i1 1 15
LV r5 i1 1 13
......
LT r6 i1 1 12
LT r9 i1 1 18
LT r0 i1 1 20

Is it possible to do so?

I got a feeling it should be possible with PROC UNIVARIATE, but not sure, how. Message was edited by: efunk
6 REPLIES 6
deleted_user
Not applicable
Hello,

One solution is offered by proc means with output out statement, combined with transpose procedure:

[pre]
data have;
input Country $ Region $ Item $ Week Sales ;
datalines;
LV r1 i1 1 12
LV r2 i1 1 11
LV r3 i1 1 10
LV r4 i1 1 15
LV r5 i1 1 13
LT r6 i1 1 12
LT r7 i1 1 10
LT r8 i1 1 11
LT r9 i1 1 18
LT r0 i1 1 20
;

proc means data=have nway noprint;
class Country Item Week;

output out=want_ntr idgroup(max(sales) out[3] (sales)=)/autoname ;

run;

proc transpose data=want_ntr out=want (drop=_name_);
by Country Item Week;

var sales:;

run;
[/pre]

Marius
efunk
Calcite | Level 5
It is essential for me that region names would be left in the table as I need to know in which region those sales were made.

There's almost zero chance for ties as all sales(currency) in real data have 13 decimal places, for example: 29.6044367170295

Message was edited by: efunk
deleted_user
Not applicable
hello,

this is not difficult. just merge "want" database with initial one. this merging will also solve the tiebreaking situation data _null_ refers at:

[pre]
proc sort data=have out=have_sort;
by Country Item Week descending Sales;
run;

proc sort data=want(rename=(col1=sales)) out=want_sort;
by Country Item Week descending Sales ;
run;

data final;
merge want_sort (in=a) have_sort (in=b);
by Country Item Week descending Sales ;

if a and b;
run;
[/pre]

Marius
efunk
Calcite | Level 5
Works perfectly. Thank you all for lightning fast support.
Special thanks to Marius.
data_null__
Jade | Level 19
What do you want in the case of ties?

You could also use the OBS info from the IDGROUP statement to POINT to the obs that you want for the subset.

[pre]
data have;
input (Country Region Item)(:$2.) Week Sales;
cards;
LV r1 i1 1 12
LV r2 i1 1 11
LV r3 i1 1 10
LV r4 i1 1 15
LV r5 i1 1 13
LT r6 i1 1 12
LT r7 i1 1 10
LT r8 i1 1 11
LT r9 i1 1 18
LT r0 i1 1 20
;;;;
run;
proc summary data=have nway;
class country;
output out=top3(keep=_obs_:) idgroup(max(sales) obs out[3](sales)=);
run;
proc print;
run;
data need;
set top3;
array obs
  • _obs_:;
    do j=1 to dim(obs);
    point=obs;
    set have point=point;
    output;
    end;
    drop _obs_: j;
    run;
    proc print;
    run;
    [/pre]

    [pre]
    Obs Country Region Item Week Sales

    1 LT r0 i1 1 20
    2 LT r9 i1 1 18
    3 LT r6 i1 1 12
    4 LV r4 i1 1 15
    5 LV r5 i1 1 13
    6 LV r1 i1 1 12 Message was edited by: data _null_;
  • darrylovia
    Quartz | Level 8
    Hello efunk

    By sorting your data and using the first./last. programming functionality you can get what you want. See the below code


    data sales;
    length country region item $2 week sales 8;
    input country $ region $ item $ week sales;
    datalines;
    LV r1 i1 1 12
    LV r2 i1 1 11
    LV r3 i1 1 10
    LV r4 i1 1 15
    LV r5 i1 1 13
    LT r6 i1 1 12
    LT r7 i1 1 10
    LT r8 i1 1 11
    LT r9 i1 1 18
    LT r0 i1 1 20
    ;
    run;

    proc sort data=sales ;
    by country descending sales;
    run;

    data what_i_want;
    set sales;
    by country descending sales;
    if first.country then do;
    counter=0;
    end;
    counter +1;

    if counter<=3;
    run;


    D

    sas-innovate-2024.png

    Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

    Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

     

    Register now!

    What is Bayesian Analysis?

    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.

    Click image to register for webinarClick image to register for webinar

    Classroom Training Available!

    Select SAS Training centers are offering in-person courses. View upcoming courses for:

    View all other training opportunities.

    Discussion stats
    • 6 replies
    • 813 views
    • 0 likes
    • 4 in conversation