SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
G_I_Jeff
Obsidian | Level 7

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?

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

4 REPLIES 4
mkeintz
PROC Star

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

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
G_I_Jeff
Obsidian | Level 7
Thank you! I've never used the SCAN function before.
Reeza
Super User

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
Super User Tom
Super User

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

sas-innovate-white.png

Special offer for SAS Communities members

Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.

 

View the full agenda.

Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 2918 views
  • 2 likes
  • 4 in conversation