Can you use FIND function in SQL?

[G_I_Jeff]

I have a variable DSNAME with value of NFCPSPPS.ROCK.PSXBC.F010

I want to create another new variable with just the first part of the string up to the period:

proc sql;                                         
 create table user.step_one as                    
 select account,                                  
        cretm,                                    
        ddsusize,                                 
        dsname,                                   
        substr(dsname,1,find('.',dsname)-1) as hlq
 from pdb.nfcplex_tape;                           

The hlq variable still contains the full string of dsname.

I am getting the following in my log:

NOTE: INVALID ARGUMENT 3 TO FUNCTION SUBSTR. MISSING VALUES MAY BE GENERATED.

Is there another function, or another way to structure this in order to get what I need?

[mkeintz · Accepted solution]

In your example, I'd suggest the scan function instead of find+substr

i.e. instead of

substr(dsname,1,find('.',dsname)-1) as hlq

use

scan(dsname,1,'.') as hlq

[G_I_Jeff]

Thank you! I've never used the SCAN function before.

[Reeza]

I think you have your parameters set backwards for the FIND() function. 

FIND(string, substring <, modifier(s)> <, start-position>)

proc sql;                                         
 create table user.step_one as                    
 select account,                                  
        cretm,                                    
        ddsusize,                                 
        dsname,                                   
        substr(dsname,1, find(dsname, '.') -1 ) as hlq
 from pdb.nfcplex_tape;  

That being said, @mkeintz  is correct and SCAN() is simpler to use in this situation. 

---

@G_I_Jeff wrote:

I have a variable DSNAME with value of NFCPSPPS.ROCK.PSXBC.F010

I want to create another new variable with just the first part of the string up to the period:

proc sql;                                         
 create table user.step_one as                    
 select account,                                  
        cretm,                                    
        ddsusize,                                 
        dsname,                                   
        substr(dsname,1,find('.',dsname)-1) as hlq
 from pdb.nfcplex_tape;                           

The hlq variable still contains the full string of dsname.

I am getting the following in my log:

NOTE: INVALID ARGUMENT 3 TO FUNCTION SUBSTR. MISSING VALUES MAY BE GENERATED.

Is there another function, or another way to structure this in order to get what I need?

---

[Tom]

You have the arguments to FIND() backwards.  You are testing if DSNAME is exactly a single period.

Are there values of DSNAME that do not include a period?

What do you want to do in that case?

data test;
  input dsname $32. ;
  test1=substr(dsname,1,find(dsname,'.')-1);
  test2=substrn(dsname,1,find(dsname,'.')-1);
  test3=scan(dsname,1,'.');
  put (_all_) (=/);
cards;
x.y
noperiod
.
;

dsname=x.y
test1=x
test2=x
test3=x
NOTE: Invalid third argument to function SUBSTR at line 244 column 9.

dsname=noperiod
test1=noperiod
test2=
test3=noperiod
RULE:       ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
250         noperiod
dsname=noperiod test1=noperiod test2=  test3=noperiod _ERROR_=1 _N_=2
NOTE: Invalid third argument to function SUBSTR at line 244 column 9.

dsname=
test1=
test2=
test3=
251         .
dsname=  test1=  test2=  test3=  _ERROR_=1 _N_=3
NOTE: The data set WORK.TEST has 3 observations and 4 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.03 seconds