How to search variable values

[Kalai2008]

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!

[novinosrin]

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

[PGStats]

---

@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.

[ballardw]

---

@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.

[Kalai2008]

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