How to check if condition using macros for a datset column

[chithra]

Hi All,

I have a condition to be added in my data step. it is as below :

IF BUSINESS_SEGMENT in <Business Segment from META_MVBS_VALID_VALUES> then do;
REL_YR_ID= 'OY';
REL_YR_VAL=strip(put(OCCYEAR, 4.));
end;
META_MVBS_VALID_VALUES is a table name.

so i modify this as below :

proc sql noprint;
select in_value into :bs_value separated by ',' from meta_mvbs_valid_values where column_name='BUSINESS_SEGMENT';
quit;

And use this macro in in the if condition right? There i am stuck.

Can you pelase guide me how to do this?

Thanks,

Chithra

[ballardw · Accepted solution]

Are your business segment values numeric or character? If your values are character then you need to have the values quoted in macro variable.

Which might look like:

proc sql noprint;
   select distinct quote(in_value) into :bs_value separated by ',' 
   from meta_mvbs_valid_values 
     where column_name='BUSINESS_SEGMENT';
quit;

which would be used in a data step as

IF BUSINESS_SEGMENT in (&bs_value.) then do;
   REL_YR_ID= 'OY';
   REL_YR_VAL=strip(put(OCCYEAR, 4.));
end;

I used DISTINCT to reduce the number of potential elements in the list of values just in case there are any duplicates. The QUOTE function is so each of the values has quotes for use in the IN comparison.

If your values are numeric then you may have to provide examples because you should control the way the macro values are built by providing an appropriate format with PUT because the default conversion that goes on in the background might not do exactly as you want, especially if there are decimal values involved.

[s_lassen]

Yes, the DISTINCT clause is a good point. However, you should trim the character values first, else you can get a very long macro string with a lot of blanks, and it is also a good idea to use single quotes rather than double quotes (see my answer).

[chithra]

this worked.

Many Thanks 🙂

[Kurt_Bremser]

This would only work for numeric values. If you work with character values, you need quotes around the individual values (use the QUOTE and STRIP function for removing leading and trailing blanks).

This will only work until the maximum length of a macro variable (64k) is reached. A proper method that will scale much further is the use of a hash object, which also removes the need for an extra SQL step.

if _n_ = 1
then do;
  declare hash bs (dataset:"meta_mvbs_valid_values (
    rename=(in_value=business_segment)
    where=(column_name='BUSINESS_SEGMENT')
  )");
  bs.definekey("business_segment");
  bs.definedone();
end;
if bs.check() = 0
then do;
  REL_YR_ID = 'OY';
  REL_YR_VAL = strip(put(OCCYEAR, 4.));
end;

Untested, since no usable (data step with datalines) example data was given.

[s_lassen]

You just need to get the values into quotes, then you have it:

select quote(trim(in_value),"'") into :bs_value separated by ',' 
from meta_mvbs_valid_values 
where column_name='BUSINESS_SEGMENT';

I used the QUOTE function rather than putting the quotation marks in directly, as this will take care of quotes in the actual values, and I used a single quote (the second parameter to the QUOTE function) rather than the standard double quote, in case your data contains stuff like "&" or "%", which the macro processor will try to interpret if values are in double quotes.

Now you should be able to use your values in an IN clause:

IF BUSINESS_SEGMENT in (&bs_value) then do;