This is a problem that I have solved but wonder if someone has an elegant solution? I suspect there may be a better solution. I have digits associated with M and B. DATA Numbers; input MB $ 1 Digit1 $ 3 Digit2 $ 4 Digit3 $ 5 ; Datalines; M 733 M 234 M 736 M 627 M 740 B 414 B 802 B 631 B 495 B 588 ; Run; I am only interested in the number of ‘M’ digits. I want to know what ‘M’ digit values are greater than ‘B’ or what ‘M’ digit values that do not appear with ‘B’. I also want to know the count or occurrence. (At the end, I multiplied by 2 because I wanted to double the count) After I separated the digits associated with M and B and got counts using Proc Freq, I spent much time looking at ‘holding’ the current record and ‘looking’ forward at the next record to do the math using set point=, etc. I also looked at Retain and Lag. Then I looked at Proc Transpose. Here are my results. Proc Print Data = Numbers; Title "Numbers"; Run; Numbers 15:53 Thursday, November 20, 2014 1 Obs MB Digit1 Digit2 Digit3 1 M 7 3 3 2 M 2 3 4 3 M 7 3 6 4 M 6 2 7 5 M 7 4 0 6 B 4 1 4 7 B 8 0 2 8 B 6 3 1 9 B 4 9 5 10 B 5 8 8 Data M (KEEP= MB Number ) B (KEEP= MB Number ); Set Numbers; Number = Digit1; If MB = "M" Then Output M; Else Output B; Number = Digit2; If MB = "M" Then Output M; Else Output B; Number= Digit3; If MB = "M" Then Output M; Else Output B; run; Data M; Set M; If Number > "0"; /*I might have a 1 or 2 digit number in the source data*/ NM=Number; Run; Proc sort data=M; By NM MB Number; Run; Data B; Set B; NB=Number; Run; Proc sort data=B; By NB MB Number; Run; Data SetMB; Set M B; By Number DESCENDING MB ; Run; Proc Print Data=SetMB; Title "Initial Data"; Run; Initial Data 15:53 Thursday, November 20, 2014 2 Obs MB Number NM NB 1 B 0 0 2 B 1 1 3 B 1 1 4 M 2 2 5 M 2 2 6 B 2 2 7 M 3 3 8 M 3 3 9 M 3 3 10 M 3 3 11 B 3 3 12 M 4 4 13 M 4 4 14 B 4 4 15 B 4 4 16 B 4 4 17 B 5 5 18 B 5 5 19 M 6 6 20 M 6 6 21 B 6 6 22 M 7 7 23 M 7 7 24 M 7 7 25 M 7 7 26 B 8 8 27 B 8 8 28 B 8 8 29 B 9 9 Proc Freq Data=SetMB Noprint; Tables MB*Number/ Out=Summary (Drop = Percent); Run; Proc Sort Data=Summary; By Number Descending MB ; Run; Proc Print Data=Summary; Title "Summary"; Run; Summary 15:53 Thursday, November 20, 2014 3 Obs MB Number COUNT 1 B 0 1 2 B 1 2 3 M 2 2 4 B 2 1 5 M 3 4 6 B 3 1 7 M 4 2 8 B 4 3 9 B 5 2 10 M 6 2 11 B 6 1 12 M 7 4 13 B 8 3 14 B 9 1 Proc Transpose Data=Summary out=wide prefix=NBR; By Number; ID MB; Var Count; Run; Data Wide; Set Wide; If NBRM > NBRB; If NBRB < 0 Then NBRB = 0; Reqd = 2 * (NBRM - NBRB); Run; /* From Summary for the Number 2, I have COUNT=2 ‘M’s and COUNT=1 ‘B’ with a difference of 1 but is 2 when multiplied by 2 */ Proc Print Data=Wide; Var Number Reqd; Title "Extra Numbers Required"; Run; Extra Numbers Required 4 15:53 Thursday, November 20, 2014 Obs Number Reqd 1 2 2 2 3 6 3 6 2 4 7 8
... View more