Hello,
I have a data set of flows between locations, locations without flows are omitted, however, I need to use the complete set of combinations in my procedure, with 0 value for observations without flows. which is the best way to generate the full collection of combinations.
Thank you,
Data Have | Data Want | ||||||
From | To | Count | From | To | Count | ||
City A | City A | 10 | City A | City A | 10 | ||
City A | City C | 15 | City A | City B | 0 | ||
City A | City D | 7 | City A | City C | 15 | ||
City B | City B | 22 | City A | City D | 7 | ||
City B | City D | 13 | City B | City A | 0 | ||
City C | City A | 35 | City B | City B | 22 | ||
City C | City B | 3 | City B | City C | 0 | ||
City C | City C | 4 | City B | City D | 13 | ||
City C | City D | 18 | City C | City A | 35 | ||
City D | City C | 25 | City C | City B | 3 | ||
City C | City C | 4 | |||||
City C | City D | 18 | |||||
City D | City A | 0 | |||||
City D | City B | 0 | |||||
City D | City C | 25 | |||||
City D | City D | 0 |
Assuming you have a source somewhere with a list of all the cities that ever appear something like:
data example; input city $; datalines; citya cityb cityc cityd citye ; proc sql; create table flow as select distinct a.city as From, b.city as To from example as a, example as b ; quit;
Example is just the name I picked for a data set with all the cities. If you don't have one let us know HOW you have your data and we can build one.
You could keep the Flow data set and then merge (data step) or join (proc sql) with other data as needed.
Try the SPARSE option in PROC FREQ.
proc freq data=have noprint;
table from * to / out = want SPARSE;
weight count;
run;
proc print data=want noobs label;
run;
EDIT: this only works if you have at least one level of the data in each of your columns. For example, if you had a destination E, but didn't have it anywhere in your data set it would not be generated. In that case you'd need to use a different approach, more similar to @ballardw solution.
Assuming you have a source somewhere with a list of all the cities that ever appear something like:
data example; input city $; datalines; citya cityb cityc cityd citye ; proc sql; create table flow as select distinct a.city as From, b.city as To from example as a, example as b ; quit;
Example is just the name I picked for a data set with all the cities. If you don't have one let us know HOW you have your data and we can build one.
You could keep the Flow data set and then merge (data step) or join (proc sql) with other data as needed.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.