topic Re: Generate combinations in SAS Programming

Generate combinations

michokwu — Wed, 22 Apr 2020 17:25:25 GMT

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

Re: Generate combinations

Reeza — Wed, 22 Apr 2020 17:35:30 GMT

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.

Re: Generate combinations

ballardw — Wed, 22 Apr 2020 17:33:21 GMT

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.

Re: Generate combinations

michokwu — Wed, 22 Apr 2020 17:45:30 GMT

This works! Thank you so much

Re: Generate combinations

michokwu — Wed, 22 Apr 2020 17:46:22 GMT

Thanks Reeza, Ballardw's solution worked