07-16-2014 08:01 PM
Hello, I have what appears to be an easy question but am getting an error. Perhaps you can help:
Below is an example--I want to subset dataset x based on pt variable common only in dataset y. I want to do this using a macro variable (I realize there are many ways to do this but I just want to do it this way)...Can you help with the error?
input pt ht;
proc sql noprint;
select distinct(pt) into :ids separated by ','
if pt in &ids then output;
I get an error message for the last datastep...Any ideas why?
Thanks very much for reading!
07-17-2014 06:09 PM
Thanks very much stat@sas...that was precisely what I was looking for...Didn't realize that the parenthesis are essential but it makes sense...macros are just text substitution so thx for your answer. It works!
07-17-2014 12:17 AM
Just keep in mind that the maximum number of characters that can be stored in a Macro Variable is 65,534. If you have a sizable dataset you may exceed this limitation and get an error.
Perhaps you could use a join, a merge or another type of lookup to ensure you don't run into this issue.
This is untested, so give it a shot and see how you go.
CREATE TABLE Y1 AS
SELECT X.PT, X.HT
FROM X WHERE X.PT IN
07-17-2014 12:56 AM
A Hash Table might also be a valid solution. The cool thing about the default functionality of the Hash Table is that it only contains unique values, so there is no need to dedupe or undertake a seperate SQL query to obtain the distinct values.
IF _N_ = 1 THEN DO;
DECLARE HASH HH(DATASET:'Y');
DO UNTIL (DONE);
SET X END=DONE;
RC = HH.CHECK();
IF RC = 0 THEN OUTPUT;
RC = HH.CHECK(); checks to see if the key appears in the Hash Table and where it does it RC is populated with 0 (if not it is populated with a number other than 0). If the values of RC is 0 then we output the values from the X dataset.
07-17-2014 06:11 PM
Thanks Scott...I really appreciate your response! Thank you so much. I didn't know you could use SQL that way to do the subset (not requiring say an inner join)...very cool for teaching me a new technique.
07-17-2014 06:14 PM
Thanks Scott for the Hash Table method....Unfortunatly I don't know much about hashes so your code is outside my limited brain capacity...However, I shall learn to use hashes & once I am comfortable enough shall revisit your solution in the future.