topic How to search variable values in SAS Programming

How to search variable values

Kalai2008 — Thu, 27 Sep 2018 16:56:13 GMT

I have a dataset having 50 variables and over a million observations. I wanted to search the cpt code value like '55963' in the entire dataset. I know the value exists in 2 or 3 variables, but not sure about the column/variable name. Basic code with filters in variable name takes longer time to process. Is there any way to code in Macro or efficient way to code.

Thanks for Looking!

Re: How to search variable values

novinosrin — Thu, 27 Sep 2018 17:12:33 GMT

That leads you to wanting to learn the concept of indexes. Welcome to the club as I have just begun(today) reading Michael Raithel(king of indexes) book "https://support.sas.com/en/books/authors/michael-raithel.html

Re: How to search variable values

PGStats — Thu, 27 Sep 2018 17:36:03 GMT

@Kalai2008 wrote:

I have a dataset having 50 variables and over a million observations. I wanted to search the cpt code value like '55963' in the entire dataset. I know the value exists in 2 or 3 variables, but not sure about the column/variable name. Basic code with filters in variable name takes longer time to process. Is there any way to code in Macro or efficient way to code.

Thanks for Looking!

Let's start from there. Please show us what code you tried.

Re: How to search variable values

ballardw — Thu, 27 Sep 2018 17:59:57 GMT

@Kalai2008 wrote:

I have a dataset having 50 variables and over a million observations. I wanted to search the cpt code value like '55963' in the entire dataset. I know the value exists in 2 or 3 variables, but not sure about the column/variable name. Basic code with filters in variable name takes longer time to process. Is there any way to code in Macro or efficient way to code.

Thanks for Looking!

One way, untested of course as not actual example data provided:

data want;
   set have;
   array _c_ _character_;
   length _vname_ $ 32.;
   do i= 1 to dim(_c_);
      if _c_[i]='55963' then do;
         _vname_= vname(_c_[i]); 
         record=_n_;
         output;
      end;
   end;
   keep _vname_ record;
run;

You might want to run proc freq on that set if the value occurs frequently.

Extensions if there are number of character values would be to create a temporary array, include a loop to test each _c_ element against the temporary array values and include the temporary array value in a value variable in the output set.

Re: How to search variable values

Kalai2008 — Thu, 27 Sep 2018 18:20:32 GMT

Thank you, it worked. I need to modify the code as the value also exists in another variable. Thanks for your inputs.