BookmarkSubscribeRSS Feed
skallamp
Obsidian | Level 7

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.

2 REPLIES 2
Ksharp
Super User

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

Alpay
Fluorite | Level 6

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;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 2 replies
  • 783 views
  • 0 likes
  • 3 in conversation