topic Re: How to calculate a division with missing data? in SAS Programming

How to calculate a division with missing data?

aminkarimid — Wed, 27 Dec 2017 15:37:11 GMT

Hello everybody;

I want to calculate a division which the nominator and denominator aren't in same rows. Please consider the table below:

table 1:

name	price	date	Rank	DP_nu	DP_nu_sum	DP_de_sum	RESULT
A	1	4/2/2008	1
A	2	4/2/2008	1	1
A	2	4/7/2008	1	0
A	2	4/17/2008	1	0
A	3	4/2/2008	1	1
A	3	4/7/2008	1	0	2		0.4
A	4	4/2/2008	2
A	4	4/2/2008	2	0
A	4	4/7/2008	2	0
A	5	4/2/2008	2	1
A	6	4/2/2008	2	1	2		0.4
A	7	4/2/2008	3
A	7	4/7/2008	3	0
A	8	4/7/2008	3	1
A	8	4/17/2008	3	0	1	5	0.2
B	1	7/22/2008	1
B	1	10/14/2008	1	0
B	2	7/22/2008	1	1
B	2	10/14/2008	1	0
B	3	10/14/2008	1	1	2		0.4
B	4	7/22/2008	2
B	4	7/22/2008	2	0
B	5	7/22/2008	2	1
B	5	10/14/2008	2	0
B	6	10/14/2008	2	1	2		0.4
B	7	7/22/2008	3
B	7	10/14/2008	3	0
B	7	10/14/2008	3	0
B	7	10/14/2008	3	0
B	8	7/22/2008	3	1
B	8	10/14/2008	3	0	1	5	0.2

I want to create the RESULT variable which is shown in the table 1 as the last column. RESULT variable is the (DP_nu_sum / DP_den_sum) for each name.

How can I do that?

Thanks in advance.

Re: How to calculate a division with missing data?

novinosrin — Wed, 27 Dec 2017 16:16:10 GMT

Good morning, Try this:

data have;

infile datalines truncover;

input name $ price date :mmddyy10. Rank DP_nu DP_nu_sum DP_de_sum ;

format date mmddyy10.;

datalines;

A 1 4/2/2008 1

A 2 4/2/2008 1 1

A 2 4/7/2008 1 0

A 2 4/17/2008 1 0

A 3 4/2/2008 1 1

A 3 4/7/2008 1 0 2

A 4 4/2/2008 2

A 4 4/2/2008 2 0

A 4 4/7/2008 2 0

A 5 4/2/2008 2 1

A 6 4/2/2008 2 1 2

A 7 4/2/2008 3

A 7 4/7/2008 3 0

A 8 4/7/2008 3 1

A 8 4/17/2008 3 0 1 5

B 1 7/22/2008 1

B 1 10/14/2008 1 0

B 2 7/22/2008 1 1

B 2 10/14/2008 1 0

B 3 10/14/2008 1 1 2

B 4 7/22/2008 2

B 4 7/22/2008 2 0

B 5 7/22/2008 2 1

B 5 10/14/2008 2 0

B 6 10/14/2008 2 1 2

B 7 7/22/2008 3

B 7 10/14/2008 3 0

B 8 7/22/2008 3 1

B 8 10/14/2008 3 0 1 5

;

proc sql;

create table want as

select *, (DP_nu_sum / max(DP_de_sum)) as result

from have

group by name

order by name, price;

quit;

Re: How to calculate a division with missing data?

Tom — Wed, 27 Dec 2017 16:16:59 GMT

How did you get such a messed up file?

Looks like the denominator is unique for each NAME, so just remerge it back onto the data.

data want ;
  merge have (drop=dp_de_sum) 
        have (keep=name dp_de_sum where=(not missing(dp_de_sum)))
  ;
  by name ;
  result=divide(dp_nu_sum,dp_de_sum);
run;

Re: How to calculate a division with missing data?

rvsidhu035 — Wed, 27 Dec 2017 16:43:31 GMT

data text;
input @1 name $1.	@5 price 1. @8 date @21 Rank 1. @25 DP_nu 1. @29 DP_nu_sum 1.	@33 DP_de_sum 1.;
informat date mmddyy.;
format date mmddyys10.;
result=DP_nu_sum /DP_de_sum;
datalines;
A	1	4/2/2008	1	 	 	 	   
A	2	4/2/2008	1	1	 	 	   
A	2	4/7/2008	1	0	 	 	   
A	2	4/17/2008	1	0	 	 	   
A	3	4/2/2008	1	1	 	 	   
A	3	4/7/2008	1	0	2	 	
A	4	4/2/2008	2	 	 	 	   
A	4	4/2/2008	2	0	 	 	   
A	4	4/7/2008	2	0	 	 	   
A	5	4/2/2008	2	1	 	 	   
A	6	4/2/2008	2	1	2	 	
A	7	4/2/2008	3	 	 	 	   
A	7	4/7/2008	3	0	 	 	   
A	8	4/7/2008	3	1	 	 	   
A	8	4/17/2008	3	0	1	5	
B	1	7/22/2008	1	 	 	 	   
B	1	10/14/2008	1	0	 	 	   
B	2	7/22/2008	1	1	 	 	   
B	2	10/14/2008	1	0	 	 	   
B	3	10/14/2008	1	1	2	 	
B	4	7/22/2008	2	 	 	 	   
B	4	7/22/2008	2	0	 	 	   
B	5	7/22/2008	2	1	 	 	   
B	5	10/14/2008	2	0	 	 	   
B	6	10/14/2008	2	1	2	 	
B	7	7/22/2008	3	 	 	 	   
B	7	10/14/2008	3	0	 	 	   
B	7	10/14/2008	3	0	 	 	   
B	7	10/14/2008	3	0	 	 	   
B	8	7/22/2008	3	1	 	 	   
B	8	10/14/2008	3	0	1	5	
;
run;