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!
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
@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.
@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.
Thank you, it worked. I need to modify the code as the value also exists in another variable. Thanks for your inputs.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.