topic Re: substr Question in SAS Enterprise Guide

substr Question

Rsadiq — Tue, 18 Apr 2017 13:12:54 GMT

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;

Re: substr Question

RW9 — Tue, 18 Apr 2017 13:16:20 GMT

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.

Re: substr Question

Tom — Tue, 18 Apr 2017 13:18:05 GMT

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;

Re: substr Question

ballardw — Tue, 18 Apr 2017 14:38:21 GMT

@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.