Combining data sets without losing observations in SAS

Question

Hye guys, I know, another post another problem :D :(.

I took a screenshot to easily explain my problem.

http://i39.tinypic.com/rhms0h.jpg

As you can see I want to merge two tables (again), the Base & Analyst table. What I want to achieve is displayed in the right bottom corner table. I’m calculating the number of total analysts and female analysts for each month in the analyst table. In the base table I have different observations for one company (here company Alcoa with ticker AA). When I use the following command:

data want;
merge base analyst;
by month ;  
run;

I get the right up corner problem. My observations in the main table are being narrowed down to only 4 observations (for each different year one observation, 2001, 2002, 2005, 2006). What I want is that the observations are not reduced but that for every year the same data is being placed as shown in the right bottom corner. What am I missing in my merge command? In both tables I have month as a time count variable ( the observations in my base table are monthly) on which I need to merge. For clarity I added 2 screenshots of my real databases in SAS.

The base table:

http://i42.tinypic.com/dr5jky.jpg

The analyst table:

http://i40.tinypic.com/eqpmqq.jpg

Here is what my merged table looks like:

http://i43.tinypic.com/116i62s.jpg

You can clearly see that the merged table only has four observations left for AA (one for each unique year) instead of the original 8.

Anyone an idea to solve this?

John John · Accepted Answer · 2010-03-24T08:48:22

Ugh, it appears you can easily solve this by merging on both ticker and month.

Data ftest;
Merge ftest tryf1 ;
By ticker month;
Run;

/shame.

Combining data sets without losing observations in SAS

1 Answers