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.
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
...
Updated my original question hopefully to clarify and answer the questions from both responses
wrap it in %eval,
%eval( &cmryear-1)
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, |
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?
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.