Create Dataset and Include Missing Variables Properly

Frequent Contributor
Posts: 92

Create Dataset and Include Missing Variables Properly

Sorry for the weird title.


I need to be able to create a SAS dataset/table to merge with other stuff later. The data has to do with airline flights and the markets they come from; we track the air traffic control fees charged be market.


Suppose the markets are 1,2,3,4,5,6,7 and each are named (for simplicity a1,a2,a3,a4,a5,a6,a7).


So there are 4 variables, Market, MarketName, Count, and Fees


Not all markets are flown each month. How can I create a table that lists all markets, names, and fees INCLUDING the ones that are 0 for that month. I had tried Proc means and Descriptives in EG, but both do not populate 0s for the months that do not have fees from a particular market. I am hoping to see:


Market    MarketName    Count   Fees

1             a1                    1            12

2             a2                    2            13

3             a3                    3            14

4             a4                    5            15

5             a5                    0            0

6             a6                    0            0

7             a7                    16         110


So the zeros must be shown


Thanks in advance.

Regular Contributor
Posts: 217

Re: Create Dataset and Include Missing Variables Properly

have one table that has all your markets defined and do a left join to the table with the fees.  Then you'll see all the markets.

Super User
Posts: 23,778

Re: Create Dataset and Include Missing Variables Properly

1. SPARSE option in PROC FREQ if the data is somewhere in the data set for any period

2. PRELOADFMT - google it

3. CLASSDATA - google it

4. Build an empty table with all the values you would need via a cross join and then merge your results with the 'empty' table. 


This is a frequently asked question, so searching on the keywords above will generate a lot of results. I would recommend searching on LexJansen instead of Google. Or search on here. 


Ask a Question
Discussion stats
  • 2 replies
  • 3 in conversation