Finding to and fro performance of a location

[Shivi82]

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.

[LinusH]

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.

[Patrick]

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)?

[Reeza]

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.

[PGStats · Accepted solution]

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;

[Shivi82]

---

@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;

---