I'm a bit confused at the final goal of the merge. Is it to find out how many patients in ClientList have MainPlatform of XOZ? There's a couple of other strategies I'd use if so:
The next example subsets the bigger dataset with an inline view that only grabs the two variables you're using and takes only the unique combinations of them prior to merging. Again I don't use big data so I don't know if memory wise this is worse or not.
PROC SQL;
CREATE TABLE merged_table AS
SELECT
t1.ID,
t1.Name,
t1.Location,
t2.MainPlatform
FROM ClientList as t1
LEFT JOIN
(select distinct ID, MainPlatform from MainPlatform
WHERE MainPlatform = 'XOZ') as t2
ON t1.ID = t2.ID;
QUIT;
The next example would add the MainPlatform variable to your ClientList dataset and then updates it wherever the ID variable is in MainPlatform instead of merging:
PROC SQL;
ALTER TABLE merged_table
ADD MainPlatform char(3);
UPDATE merged_table
set MainPlatform='XOZ'
where ID in (select ID from MainPlatform where MainPlatform='XOZ');
QUIT;
Neither of these examples help as much if the number of XOZ rows in MainPlatform matter for a particular ID.
... View more
