topic Re: Top 10 retailors according to Script Count for Tpye in SAS Programming

Top 10 retailors according to Script Count for Tpye

skallamp — Fri, 06 Jul 2012 06:40:09 GMT

I have a SAS input as shown in Input tab. It has 2 types, Type A and Type B.

I need to get the top 10 retailors according to script count and 11th raw should be sum of all other retailors and their counts. I need to get this report for both Type A(Output1) and TypeB(Output2).

Total number of raws will vary for each tasks. How do we write a SAS or Proc SQL for this job.

Any input would be a great help.

Re: Top 10 retailors according to Script Count for Tpye

Ksharp — Fri, 06 Jul 2012 07:37:21 GMT

How about:

proc import datafile='c:\type_data.xls' out=have dbms=excel  replace;getnames=yes;run;

proc sort data=have;by type descending Script_Count;run;
data temp(drop=i _:);
i=0;_Script_Count=0; _Year2011=0;_Year2012=0;
do until(last.type);
 set have;
 by type;
 i+1;
 if i lt 11 then output;
  else do;
          _Script_Count+Script_Count;
          _Year2011+Year2011;
          _Year2012+Year2012;
          if last.type then do;
                    Script_Count=_Script_Count;
                    Year2011=_Year2011;
                    Year2012=_Year2012;
                    Retailors="Other Retailors";
                    output;
                            end;
        end;
end;
run;
data _null_;
if 0 then set temp;
declare hash ha(hashexp:16,ordered:'a');
 ha.definekey('k');
 ha.definedata('type','Retailors','Script_Count','Year2011','Year2012');
 ha.definedone();
do until(last.type);
 set temp;
 by type;
 k+1;
 ha.add();
end;
ha.output(dataset: compress(type, ,'ka'));
run;

Ksharp

Re: Top 10 retailors according to Script Count for Tpye

Alpay — Fri, 06 Jul 2012 07:38:59 GMT

proc sort data=have ;

by Type descending Script_Count;

run;

data step;

set have;

by Type;

if first.Type then N = 0;

if N <= 10 then N + 1;

retain N;

if N > 10 then Retailors = 'Others';

run;

proc summary data=step nway;

class Type N Retailors;

var Script_Count Year2011 Year2012;

output out=want(drop=_: N)

sum=;

run;

proc print; run;