Desktop productivity for business analysts and programmers

substr Question

Reply
Occasional Contributor
Posts: 19

substr Question

Hello how would this column resp_yr be turned into a character value so that this substr can work?

 

Thank you

 

Rida

 

 

 

NOTE: %INCLUDE (level 1) ending.
394       
395        PROC SQL;
396         CREATE TABLE rptlib.sum_responses_curryr AS
397         SELECT *
398         FROM outlib.sum_responses_all_yrly
399         WHERE SUBSTR(resp_yr,1,4) = '&begyr.'
400        ;
ERROR: Function SUBSTR requires a character expression as argument 1.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
400      !  QUIT;

Esteemed Advisor
Esteemed Advisor
Posts: 7,249

Re: substr Question

This is a very basic topic, converting number to character and vice versa.  The put and input functions are respectively used:

where substr(put(RESP_YR,best.),1,4)='&begyr.'

Note the put() which puts the numeric resp_yr into the best. format.

Super User
Super User
Posts: 6,368

Re: substr Question

So RESP_YR is a number.

Assuming it is the YEAR (ie 2010 or 2017 for example).

Assuming that BEG_YR also a four digit string that looks like a year.

 

 

 

WHERE resp_yr = &begyr

Example.

data have;
  input resp_yr @@ ;
cards ;
2010 2013 2014 2015 
;
%let begyr=2013;

data want ;
 set have ;
 where resp_yr = &begyr ;
run;
Grand Advisor
Posts: 10,239

Re: substr Question


Rsadiq wrote:

Hello how would this column resp_yr be turned into a character value so that this substr can work?

 

Thank you

 

Rida

 

 

 

NOTE: %INCLUDE (level 1) ending.
394       
395        PROC SQL;
396         CREATE TABLE rptlib.sum_responses_curryr AS
397         SELECT *
398         FROM outlib.sum_responses_all_yrly
399         WHERE SUBSTR(resp_yr,1,4) = '&begyr.'
400        ;
ERROR: Function SUBSTR requires a character expression as argument 1.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
400      !  QUIT;


Also note that = '&begyr.' will not likely get what you want any way as with single quotes the value compared would be &begyr NOT the resolved value of the macro variable begyr. Use = "&begyr."   

 

And what kind of values does the variable resp_yr take anyway? If they are numeric actual years (2017)

then

where resp_yr = &begyr. should work just fine.

Ask a Question
Discussion stats
  • 3 replies
  • 133 views
  • 0 likes
  • 4 in conversation