Getting the difference in datastep

[Q1983]

data have;
input loan_num sales returns;
datalines;
112 12 0
113 11 10
114 9 5
;run;

data have2;
set have;
diff=sales-returns;
run;

diff=sales-returns works in this example.  In my actual program  I created a variable named diff.  I subtract sales and returns.  Both are defined as numeric.  It calculates correctly if both numbers are >0, however if one of the numbers is a 0 the diff is shown as 0 which is incorrect.  My question is would there be a different method of subtracting the two variables.  Intck is appropriate for dates, not two numerics as shown here.

[Quentin]

Hi,

Can you post an example which shows the result you are surprised by?

diff=sales-returns;

Should work fine.  If Sales is 0 and Returns is a positive number, Diff will be a negative number, it won't be set to 0.

e.g. this code:

data have;
input loan_num sales returns;
datalines;
112 12 0
113 0 10
114 0 0
;run;

data have2;
set have;
diff=sales-returns;
put (sales returns diff)(=) ;
run;

returns:

9    data have2;
10   set have;
11   diff=sales-returns;
12   put (sales returns diff)(=) ;
13   run;

sales=12 returns=0 diff=12
sales=0 returns=10 diff=-10
sales=0 returns=0 diff=0

[CarlosSpranger]

Hello @Q1983,

I was playing with your challenge, and its quite difficult to figure out your need as you describe on the text. But I managed to add a new line with a Zero on Sales Variable to check and the behavior is as expected; The Diff calculates properly.

data have;
input loan_num sales returns;
datalines;
112 12 0
112 0 12 /*New Line with Value=Zero on Sales Variable*/
113 11 10
114 9 5;
run;

And When I Check the Execution iteractions all seem well:

loan_num=112 sales=12 returns=0 diff=12         =>  1st Iteraction is correct

loan_num=112 sales=0 returns=12 diff= -12       =>  2nd Iteraction is correct

loan_num=113 sales=11 returns=10 diff=1         =>   3rd Iteraction is correct

loan_num=114 sales=9 returns=5 diff=4              =>  4th Iteraction is also correct

Can you please elaborate?

regards,

CS

[Q1983]

I had to give the previous example however here is the actual code

data test(keep=acct_nbr tot_accts_cnt_trn no_act_cnt_ no_acct_ind);
set dep.cmb1;
if acct_nbr in('54', '43');
no_acct_ind=(tot_accts_cnt_trn-no_act_cnt_);
run;

The output is something like this

ACCT_NBR	tot_accts_cnt_trn	no_act_cnt_	no_acct_ind
54	159	17	142
54	159	17	142
54	159	17	142
43	1
43	1
43	1

One thing I notice is that it shows a 0 in the last 2 columns for acct_nbr 43 in sas however when I export to excel it shows a blank value

Its as if the formula is not working with a 0 exists.  I have no idea why this is happening.  As you can see it worked for acct_nbr 54

[CarlosSpranger]

Can you share a sample input related to this last output?

I Think the issue resides in your input data.

I recreated the scenario using proc sql and it works.

data have;
input acct_nbr tot_accts_cnt_trn no_act_cnt_ no_acct_ind;
Datalines;
run;

proc sql;
insert into have values (43,1,1,.);
quit;

data test(keep=acct_nbr tot_accts_cnt_trn no_act_cnt_ no_acct_ind);
set have;
no_acct_ind=tot_accts_cnt_trn-no_act_cnt_;
run;

acct_nbr=54 tot_accts_cnt_trn=159 no_act_cnt_=17 no_acct_ind=142           => 1st  Iteraction

acct_nbr=54 tot_accts_cnt_trn=159 no_act_cnt_=17 no_acct_ind=142           => 2nd Iteraction

acct_nbr=54 tot_accts_cnt_trn=159 no_act_cnt_=17 no_acct_ind=142          => 3rd Iteraction

acct_nbr=43 tot_accts_cnt_trn=1 no_act_cnt_=. no_acct_ind=.                        => 4th Iteraction

acct_nbr=43 tot_accts_cnt_trn=1 no_act_cnt_=. no_acct_ind=.                        => 5th Iteraction

acct_nbr=43 tot_accts_cnt_trn=1 no_act_cnt_=. no_acct_ind=.                        => 6th Iteraction

acct_nbr=43 tot_accts_cnt_trn=1 no_act_cnt_=1 no_acct_ind=0                      => 7th Iteraction

On the one with . which indicate missing values is expected because the subtraction expression should return missing values

I guest you should do some dta cleansing on input Data.

Regards

CS

[ed_sas_member]

Hi @Q1983 

Can you try to reset the missing option? Maybe it is unfortunately set to 0?

Can you share you code to export to Excel?

options missing="";

Best,