DATA Step, Macro, Functions and more

Variable conversion and operation

Accepted Solution Solved
Reply
Contributor
Posts: 53
Accepted Solution

Variable conversion and operation

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.


Accepted Solutions
Solution
‎06-16-2014 11:45 AM
Super User
Posts: 17,868

Re: Variable conversion and operation

wrap it in %eval,

%eval( &cmryear-1)

View solution in original post


All Replies
Super User
Super User
Posts: 7,407

Re: Variable conversion and operation

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

...

Contributor
Posts: 53

Re: Variable conversion and operation

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

Solution
‎06-16-2014 11:45 AM
Super User
Posts: 17,868

Re: Variable conversion and operation

wrap it in %eval,

%eval( &cmryear-1)

Contributor
Posts: 53

Re: Variable conversion and operation

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,
Super User
Posts: 10,516

Re: Variable conversion and operation

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?

Super User
Super User
Posts: 6,502

Re: Variable conversion and operation

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 263 views
  • 0 likes
  • 5 in conversation