Help using Base SAS procedures

Finding to and fro performance of a location

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 92
Accepted Solution

Finding to and fro performance of a location

Dear Team, Good Morning.

 

I work for a supply chain company. I am currently working on a project where i need to know how much a destination is receiving weight from other locations and similarly what it is sending to other locations.

for example:

Part1: I have city as "Bangalore" which is sending weight to all parts of India- City A, B, C etc. hence i need to know what is total weight sent to City A from bangalore, City B etc.

 

Part 2: When we have "Bangalore" as a destination then it is receiving from various cities as mentioned above- City A, B, C etc.

 

So i need to know what is actual amount and in % what is being sent from bangalore to various destinations and also which destinations are the highest load supplier to bangalore.

I hope i am able to narrate my requirement correctly.

 

Below attached is some sample data.

Thanks for the help.

 

 


Accepted Solutions
Solution
‎11-08-2015 11:39 PM
Respected Advisor
Posts: 4,935

Re: Finding to and fro performance of a location

Assuming your data is in table toFroData, you could get those statistics with a few queries:

 


%let location=BANGALORE;

title "To and fro performance of &location.";
proc sql;
create table to as
select upcase(destination) as toCity, sum(totalFrt) as toFrt
from toFroData
where upcase(city) = "&location"
group by toCity;
create table fro as
select upcase(city) as froCity, sum(totalFrt) as froFrt
from toFroData
where upcase(destination) = "&location"
group by froCity;
create table want as
select 
    coalesce(toCity, froCity) as City , 
    froFrt, 
    froFrt / sum(froFrt) as froPct format=percentn7.2,
    toFrt,
    toFrt / sum(toFrt) as toPct format=percentn7.2
from to full join fro on toCity=froCity;
select * from want;
quit;
PG

View solution in original post


All Replies
Super User
Posts: 5,441

Re: Finding to and fro performance of a location

It sounds like you are asking someone to design a solution for you. And I don't think that the forum is the right place for that. Try to get started and to narrow down to more SAS Specific questions.
Design is need to be done within the scope of your project / system.
Data never sleeps
Respected Advisor
Posts: 4,173

Re: Finding to and fro performance of a location

[ Edited ]

On a high level it sounds easy enough to create sums by sender or receiver. How hard this is going to be in reality comes all down to your data.

 

You count in weight and not units. So where get the weights registered and can there be a difference between sender and receiver for the same freight. What about wastage and loss? How good is your data quality and how complete is your data? How is your data organized and do you need some data transformation before you can report on it?

 

So first step is: What do you need (=requirements)? What do you have? How do you get from have to need (that's your design)?

 

Super User
Posts: 19,877

Re: Finding to and fro performance of a location

I think it's a proc freq or proc tabulate and specifying the appropriate weight and percentage to calculate. It works best if you present a sample input and sample output data set, ideally they align.
Solution
‎11-08-2015 11:39 PM
Respected Advisor
Posts: 4,935

Re: Finding to and fro performance of a location

Assuming your data is in table toFroData, you could get those statistics with a few queries:

 


%let location=BANGALORE;

title "To and fro performance of &location.";
proc sql;
create table to as
select upcase(destination) as toCity, sum(totalFrt) as toFrt
from toFroData
where upcase(city) = "&location"
group by toCity;
create table fro as
select upcase(city) as froCity, sum(totalFrt) as froFrt
from toFroData
where upcase(destination) = "&location"
group by froCity;
create table want as
select 
    coalesce(toCity, froCity) as City , 
    froFrt, 
    froFrt / sum(froFrt) as froPct format=percentn7.2,
    toFrt,
    toFrt / sum(toFrt) as toPct format=percentn7.2
from to full join fro on toCity=froCity;
select * from want;
quit;
PG
Frequent Contributor
Posts: 92

Re: Finding to and fro performance of a location


PGStats wrote:

Assuming your data is in table toFroData, you could get those statistics with a few queries:

 


%let location=BANGALORE;

title "To and fro performance of &location.";
proc sql;
create table to as
select upcase(destination) as toCity, sum(totalFrt) as toFrt
from toFroData
where upcase(city) = "&location"
group by toCity;
create table fro as
select upcase(city) as froCity, sum(totalFrt) as froFrt
from toFroData
where upcase(destination) = "&location"
group by froCity;
create table want as
select 
    coalesce(toCity, froCity) as City , 
    froFrt, 
    froFrt / sum(froFrt) as froPct format=percentn7.2,
    toFrt,
    toFrt / sum(toFrt) as toPct format=percentn7.2
from to full join fro on toCity=froCity;
select * from want;
quit;

 

🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 364 views
  • 1 like
  • 5 in conversation