BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Shivi82
Quartz | Level 8

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

5 REPLIES 5
LinusH
Tourmaline | Level 20
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
Patrick
Opal | Level 21

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
Super User
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
Opal | Level 21

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
Shivi82
Quartz | Level 8

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

 

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 993 views
  • 1 like
  • 5 in conversation