topic Re: Create Dataset and Include Missing Variables Properly in SAS Programming

Create Dataset and Include Missing Variables Properly

BCNAV — Fri, 23 Feb 2018 17:44:18 GMT

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.

Re: Create Dataset and Include Missing Variables Properly

tomrvincent — Fri, 23 Feb 2018 19:46:58 GMT

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.

Re: Create Dataset and Include Missing Variables Properly

Reeza — Fri, 23 Feb 2018 20:08:15 GMT

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.