Create Dataset and Include Missing Variables Properly

Reply
Frequent Contributor
Posts: 81

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.

Frequent Contributor
Posts: 125

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: 22,579

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
  • 94 views
  • 0 likes
  • 3 in conversation