topic Re: Variable conversion and operation in SAS Programming

Variable conversion and operation

dsbihill — Mon, 16 Jun 2014 14:19:58 GMT

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.

Re: Variable conversion and operation

RW9 — Mon, 16 Jun 2014 14:33:49 GMT

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

...

Re: Variable conversion and operation

ballardw — Mon, 16 Jun 2014 14:39:50 GMT

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?

Re: Variable conversion and operation

Tom — Mon, 16 Jun 2014 14:45:09 GMT

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

Re: Variable conversion and operation

dsbihill — Mon, 16 Jun 2014 15:05:55 GMT

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

Re: Variable conversion and operation

Reeza — Mon, 16 Jun 2014 15:45:12 GMT

wrap it in %eval,

%eval( &cmryear-1)

Re: Variable conversion and operation

dsbihill — Mon, 16 Jun 2014 17:27:38 GMT

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,