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

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  
        
FromToCount  FromToCount
City ACity A10  City ACity A10
City ACity C15  City ACity B0
City ACity D7  City ACity C15
City BCity B22  City ACity D7
City BCity D13  City BCity A0
City CCity A35  City BCity B22
City CCity B3  City BCity C0
City CCity C4  City BCity D13
City CCity D18  City CCity A35
City DCity C25  City CCity B3
     City CCity C4
     City CCity D18
     City DCity A0
     City DCity B0
     City DCity C25
     City DCity D0

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

4 REPLIES 4
Reeza
Super User

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
Quartz | Level 8
Thanks Reeza, Ballardw's solution worked
ballardw
Super User

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
Quartz | Level 8
This works! Thank you so much

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 640 views
  • 0 likes
  • 3 in conversation