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.
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
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:
regards,
CS
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
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;
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
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,
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.