Contributor
Posts: 36

# 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;

Super User
Posts: 9,812

## 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
Posts: 8,264

## 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;``````
Super User
Posts: 13,889

## Re: 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;

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.

Discussion stats
• 3 replies
• 318 views
• 0 likes
• 4 in conversation