topic Re: Can you use FIND function in SQL? in SAS Programming

Can you use FIND function in SQL?

G_I_Jeff — Tue, 01 Jun 2021 14:38:48 GMT

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?

Re: Can you use FIND function in SQL?

mkeintz — Tue, 01 Jun 2021 14:49:25 GMT

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

Re: Can you use FIND function in SQL?

G_I_Jeff — Tue, 01 Jun 2021 14:58:26 GMT

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

Re: Can you use FIND function in SQL?

Reeza — Tue, 01 Jun 2021 14:58:43 GMT

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?

Re: Can you use FIND function in SQL?

Tom — Tue, 01 Jun 2021 15:01:55 GMT

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