topic Re: Creating a variable substracting 2 variables in SAS Programming

Creating a variable substracting 2 variables

MalcolmCICWF — Mon, 30 Sep 2013 20:34:01 GMT

I am trying to create a field called Varience that is Actual Payments minus Original Forecast (Trantype). All of my numbers below are made up of a transaction 'amount' with a 'trandate'. I am trying to use my existing data sets to pull in all the Actual Payments, another data set with Original Forecast, then a dataset where I try to create the 'Trantype' of Variance, subtracting a.amount from b.amount.

Below is an example of what I am trying to do for code. My data is working with the seperate data sets, but when I join and plave into my Qlikview report, I am not getting the results I want. The trantype is not ending up as "Variance".

Re: Creating a variable substracting 2 variables

Reeza — Mon, 30 Sep 2013 22:35:59 GMT

The code looks correct to me, so its changing somewhere else.

I don't know what you mean by this though:

"but when I join and plave into my Qlikview report"

Re: Creating a variable substracting 2 variables

ballardw — Mon, 30 Sep 2013 22:49:27 GMT

Are you getting a message in the log about variable trantype already in the data set? You have 3 versions, one from each input set a and b and the one you create with "as". A quick test with small data sets makes me bet you are getting the trantype as 'ActualPayments'. Drop the trantype from the two input sets and retry.

Re: Creating a variable substracting 2 variables

MalcolmCICWF — Wed, 02 Oct 2013 17:12:59 GMT

Ok let me simplify what I am coming accross right now. I have 2 data sets right now, one has "amount1" and the other "amount2". Both are formatted as numeric and I am looknig for the format to subtract 1 from 2. I thought I knew what it is, but I am not receiving any results. What am I doing wrong?

Re: Creating a variable substracting 2 variables

MalcolmCICWF — Wed, 02 Oct 2013 17:13:53 GMT

Sorry in the example it shows I am adding, but this was just a test to see if even that worked.

Re: Creating a variable substracting 2 variables

Linlin — Wed, 02 Oct 2013 18:45:53 GMT

you should use "merge" instead of "set".

Re: Creating a variable substracting 2 variables

MalcolmCICWF — Wed, 02 Oct 2013 21:10:34 GMT

Thanks Linilin, I was just experimenting with this when I saw your reply. It looks like I am getting the results I want now.