topic Re: Getting the difference in datastep in SAS Programming

Getting the difference in datastep

Q1983 — Sun, 17 May 2020 23:01:22 GMT

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.

Re: Getting the difference in datastep

Quentin — Sun, 17 May 2020 23:53:33 GMT

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

Re: Getting the difference in datastep

CarlosSpranger — Mon, 18 May 2020 00:17:23 GMT

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,

Re: Getting the difference in datastep

Q1983 — Mon, 18 May 2020 02:36:49 GMT

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

Re: Getting the difference in datastep

CarlosSpranger — Mon, 18 May 2020 03:32:25 GMT

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

Re: Getting the difference in datastep

ed_sas_member — Mon, 18 May 2020 08:55:27 GMT

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,