05-14-2014 05:05 AM
I am new to SAS and have this basic problem. I have a list of NYSE trading dates in table A as follows -
I have another table B that has share price information as -
... has numeric data related to price and returns.
Now I need to join the NYSE Data table to the above table to get the following table -
i.e. a simple left join. The zero's will be filled with . in SAS to indicate missing values, but you get the idea. But if I use the following command -
The join happens only for the first ID (i.e. ID=1) while for the rest of the IDs, the same data is maintained. But I need to insert the trade dates for all IDs.
How can get the final data without running a do while loop for all IDs? I have 1000 IDs and looping and joining 1000 times is not an option due to limited memory.
05-14-2014 05:36 AM
If you want all observations from either than do:
create table want as
select COALESCE(FIRST_TABLE.ID,SECOND_TABLE.ID) as ID,
full join SECOND_TABLE
The full join means all rows from both tables are merged on id giving full list, you do need the coalesce to get id populated, as if you just take first table then it would be missing for those which appear only in second table.
05-14-2014 11:01 AM
You should use a CROSS JOIN to get all combinations of ID and trading_date and then LEFT JOIN to your trading data :
create table joined as
select I.ID, a.trading_date as date, b.price
(select unique ID from b) as I cross join
a left join
b on I.ID=b.ID and a.trading_date=b.date
order by ID, date;