Generate combinations

[michokwu]

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

[Reeza]

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.

[michokwu]

Thanks Reeza, Ballardw's solution worked

[ballardw · Accepted 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.

[michokwu]

This works! Thank you so much