Variable conversion and operation

[dsbihill]

I have the following section of a proc sql.  The bold section below i am having trouble making it work.  I created a variable cmryear that contains a 4 digit year.  Depending on the date of the observation i may want to subtract one from the year for my cmrperiod variable.  Does anyone have a suggestion as to how i can do this.  I have tried put and input and just seem get errors when i do.

I basicly want to subtract 1 from &cmryear variable for observations falling into the date range.  The problem is that It doesnt like the &cmr year because it is a character value so it has to be converted..

Thank you all for your time.

%let cmr_period = "CN/KTZ 2016 CMR"

%let cmryear= %sysfunc(compress(&cmr_period ,"/",A));

%put output of cmryear from log is  2016

proc sql,

---other stuff

     Case

            when "&date_range_min"d <= datepart(transaction_date) <= "&date_range_max"d then catx(" ",mat.customer, "&cmryear", "CMR")

            when intnx("year","&date_range_min"d,-1,"sameday") <= datepart(transaction_date) < "&date_range_Min"d then catx(" ", &cmryear-1, "CMR")

        end as CMRPeriod,

---other stuff

quit;

log error  - ERROR: Expression using subtraction :smileyminus: requires numeric types.

[RW9]

Yep, without seeing the errors am guessing, but copying and pasting the %let into the statement yields this:

then catx(" ",mat.customer, input(put(%sysfunc(compress(&cmr_period ,"/",A)),4.)-1,$4.), "CMR")

Which doesn't look right - e.g. what is the &cmr_period. Also, you have your inputs and put's mized up, input reads char into num, put outputs num as char.

Might I suggest you do it bit by bit. e.g:

proc sql;

     create table want as

     select     input(strip(tranwrd(CMR_PERIOD,"/","")),best.) as MY_YEAR,

                   case     when ... then CALCULATED MY_YEAR

                               else CALCULATED MY_YEAR - 1 end as ALTERED_YEAR,

                    catx(" ",MAT.CUSTOMER,strip(put(ALTERED_YEAR,best.)),"CMR") as FINAL_RESULT

...

[dsbihill]

Updated my original question hopefully to clarify and answer the questions from both responses

[Reeza · Accepted solution]

wrap it in %eval,

%eval( &cmryear-1)

[dsbihill]

Works perfectly thank you

	Case
		when "&date_range_min"d <= datepart(transaction_date) <= "&date_range_max"d
		then catx(" ",mat.customer, "&cmryear", "CMR")
		when intnx("year","&date_range_min"d,-1,"sameday") <= datepart(transaction_date) < "&date_range_Min"d
		then catx(" ",mat.customer, %eval(&cmryear-1), "CMR")
	end as CMRPeriod,

[ballardw]

You might provide some examples of whay your &cmryear variable actually looks like such as with %put.

Depeding on how/when you create it you might be better off creating a second variable: %let cmryearminus= %eval(&cmryear-1);

And since many people asking about stuff in SQL sometimes take a long time to reveal they are attempting operations in another DBMS, is this actual SAS dataset you are using or is this passthrough or from another DBMS?

[Tom]

Does the value of CMRYEAR actually look like YYYY?

If so then there you do not need to treat it as a character string in the SQL code.  You can pass an expression to the CATX() function.

case

  when "&date_range_min"d <= datepart(transaction_date) <= "&date_range_max"d

    then catx(" ",mat.customer, &cmryear , "CMR")

  when intnx("year","&date_range_min"d,-1,"sameday") <= datepart(transaction_date) < "&date_range_Min"d

    then catx(" ",mat.customer, &cmryear - 1 , "CMR")

  else ' '

end as CMRPeriod