find if corresponding variable exist

[laiguanyu001]

Hi,

My goal is to find if a variable exists in a dataset 

For example here is a list of variable names : oneabc onebcd twoabc twobcd......

now I know that abc and bcd are common suffix, I want to find out that if a variable name with suffix abc exists, does its corresponding variable name with suffix bcd also exist? Sorry this might sound confusing. Basically what I want to know is:

1.find variable with suffix abc

2. now I know that oneabc and twoabc exist

3. how do I find if onebcd and twobcd exist?  

[Reeza]

It's very hard to understand what you're trying to do here, but the list of names is available via sashelp.vcolumn or dictionary.column table so you can see the list of variable names quite easily.

If you describe more of what you're trying to do we can likely help. FYI - SAS and most other languages work better with prefixes rather than suffixes, so if you have a choice of naming conventions, use prefixes, not suffixes.

[laiguanyu001]

Let me rephrase it. If a variable with suffix abc is present, I want to check if its associated variable with suffix bcd exists. 

example: if oneabc exists in data "have", does onebcd exist in data "have"? 

Ultimately I want to write a macro, so I can check for different suffix and avoid the eyeballing work. 

Sorry that was confusing, it is a difficult problem! (for me at least) 

[Reeza]

I think this is what you're saying, I havea variable with a prefix of "one". Is there other variables, such as onebcd, with the same prefix?

How do you that prefix is one? Is it always three characters? How can that be generalized?

[ballardw]

Are you expecting these "matched" variables to be in the same data set? A different data set but in the same Library? In any Library.Dataset combination(s).

This code will find all of the variable names that end in a given string, the library and data set names for a given library. The library name should be in upper case as that is how the metadata in the dictionary.columns is stored.

proc sql;
   create table suffixdata as
   select upcase(name),memname, libname
   from dictionary.columns
   where libname='SOMELIB'
    and find(name,'ABC','i',-32);
quit;

With responses to my first questions we can work on the next step but that would be comparing the start of variables (substring of the name from 1 to (length of the name - length of the suffix +1 character).

[laiguanyu001]

Hi,thanks for the response! These variables are in the same data sets. I think I understand what you are suggesting for the next step, and I think it's what I want, but I still don't really know how to execute that. 

[ballardw · Accepted solution]

---

@laiguanyu001 wrote:

Hi,thanks for the response! These variables are in the same data sets. I think I understand what you are suggesting for the next step, and I think it's what I want, but I still don't really know how to execute that. 

---

You would create two data sets similar to the suffixdata, one for each suffix and then something similar to

proc sql;
   create table twovars as
   select a.libname, a.memname, a.name, b.name as nameb
   from suffixdata1 as a, suffixdata2 as b
   where   a.libname=b.libname
      and  a.memname=b.memname 
      and substr(a.name,1,(index(a.name,'ABC')-1))
        = substr(b.name,1,(index(b.name,'BCD')-1))
   ;
quit;

The find function could be used as well as the index function.