PROC SQL

Accepted Solution Solved
Reply
Contributor
Posts: 50
Accepted Solution

PROC SQL

HI, 

what should be the code for this question ?

 

In the Airlines_grouping file the data provided in the first tab has to be summarized like in the output tab :

1) Airlines to be clubbed as 2  entitles group1: JET Airways  and Air India  and group 2Smiley Surprisedthers 

2) Top 15 clients to be identified location-wise  based upon the total volume and the airline - group share 

 

i am attaching a Airlines_grouping excel file ?

 


Accepted Solutions
Solution
‎07-06-2016 07:26 AM
Super User
Posts: 9,676

Re: PROC SQL

proc import datafile='/folders/myfolders/Airlines_grouping.xls' dbms=xls replace out=have;
run;

ods select none;
ods output SQL_Results=want;
proc sql number outobs=15;
 select Client_Name,
 sum(case when Airlines in ('Jet Airways' 'Air India') then Amout  else 0 end) as group1,
 sum(case when Airlines not in ('Jet Airways' 'Air India') then Amout  else 0 end) as group2,
 sum(calculated group1,calculated group2) as Total ,
 calculated group1/calculated total as pct_group1 format=percent8.2, 
 calculated group2/calculated total as pct_group2 format=percent8.2                 
  from have
   group by Client_Name
    order by calculated total desc;
quit;
ods select all;

View solution in original post


All Replies
Super User
Posts: 17,796

Re: PROC SQL

What have you tried?

please post data in post not as an attachment. 

Solution
‎07-06-2016 07:26 AM
Super User
Posts: 9,676

Re: PROC SQL

proc import datafile='/folders/myfolders/Airlines_grouping.xls' dbms=xls replace out=have;
run;

ods select none;
ods output SQL_Results=want;
proc sql number outobs=15;
 select Client_Name,
 sum(case when Airlines in ('Jet Airways' 'Air India') then Amout  else 0 end) as group1,
 sum(case when Airlines not in ('Jet Airways' 'Air India') then Amout  else 0 end) as group2,
 sum(calculated group1,calculated group2) as Total ,
 calculated group1/calculated total as pct_group1 format=percent8.2, 
 calculated group2/calculated total as pct_group2 format=percent8.2                 
  from have
   group by Client_Name
    order by calculated total desc;
quit;
ods select all;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 373 views
  • 1 like
  • 3 in conversation