BookmarkSubscribeRSS Feed
BCNAV
Quartz | Level 8

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.

2 REPLIES 2
tomrvincent
Rhodochrosite | Level 12

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.

Reeza
Super User

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. 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 325 views
  • 0 likes
  • 3 in conversation