Hi,..Suppose I have several boxes of marbles and would like to put them in separate bags based on the color of the marble. I would like to put specific colors in each bag where the bag is assigned a BagNumber based on the specific GrNumber for one of the colors that will be assigned to each bag. I have tried the code below but I am not getting all of BagNumbers assigned. Any suggestions on how to get all BagNumbers assigned....Thanks.
data Have; infile datalines truncover; input BoxNumber Color $ GrNumber; datalines; 1 Red . 1 Orange . 1 Orange 1 1 Red . 1 Blue . 1 Black . 1 Blue . 1 Black . 1 Black . 1 White 2 1 Black . 1 White . 1 Green 3 1 Yellow . 1 White . 2 Red . 2 Orange 1 2 Blue . 2 Black . 2 Blue . 2 White . 2 Black . 2 White 2 ; proc sort data=Have; by BoxNumber Color; run; data Want; do _n_ = 1 by 1 until (last.Color); set Have; by BoxNumber Color; if GrNumber in (1) and Color in ('Red','Orange','Blue') and missing(BagNumber) then BagNumber=1; else if GrNumber in (2) and Color in ('White','Black') and missing(BagNumber) then BagNumber=2; else if GrNumber in (3) and Color in ('Green','Yellow') and missing(BagNumber) then BagNumber=3; output; end; run; Want: BoxNumber Color GrNumber BagNumber 1 Black 2 1 Black 2 1 Black 2 1 Black 2 1 Blue 1 1 Blue 1 1 Green 3 3 1 Orange 1 1 Orange 1 1 1 Red 1 1 Red 1 1 White 2 2 1 White 2 1 White 2 1 Yellow 3 2 Black 2 2 Black 2 2 Blue 1 2 Blue 1 2 Orange 1 1 2 Red 1 2 White 2 2 White 2 2
You code isn't doing what you want because you have 3 comparisons that all start with "if GrNumber in (<some value>)" but most of your data is missing GrNumber.
So what is the rule of what is supposed to be done when the GrNumber is missing?
@twildone wrote:
Hi,..Suppose I have several boxes of marbles and would like to put them in separate bags based on the color of the marble. I would like to put specific colors in each bag where the bag is assigned a BagNumber based on the specific GrNumber for one of the colors that will be assigned to each bag. I have tried the code below but I am not getting all of BagNumbers assigned. Any suggestions on how to get all BagNumbers assigned....Thanks.
data Have; infile datalines truncover; input BoxNumber Color $ GrNumber; datalines; 1 Red . 1 Orange . 1 Orange 1 1 Red . 1 Blue . 1 Black . 1 Blue . 1 Black . 1 Black . 1 White 2 1 Black . 1 White . 1 Green 3 1 Yellow . 1 White . 2 Red . 2 Orange 1 2 Blue . 2 Black . 2 Blue . 2 White . 2 Black . 2 White 2 ; proc sort data=Have; by BoxNumber Color; run; data Want; do _n_ = 1 by 1 until (last.Color); set Have; by BoxNumber Color; if GrNumber in (1) and Color in ('Red','Orange','Blue') and missing(BagNumber) then BagNumber=1; else if GrNumber in (2) and Color in ('White','Black') and missing(BagNumber) then BagNumber=2; else if GrNumber in (3) and Color in ('Green','Yellow') and missing(BagNumber) then BagNumber=3; output; end; run; Want: BoxNumber Color GrNumber BagNumber 1 Black 2 1 Black 2 1 Black 2 1 Black 2 1 Blue 1 1 Blue 1 1 Green 3 3 1 Orange 1 1 Orange 1 1 1 Red 1 1 Red 1 1 White 2 2 1 White 2 1 White 2 1 Yellow 3 2 Black 2 2 Black 2 2 Blue 1 2 Blue 1 2 Orange 1 1 2 Red 1 2 White 2 2 White 2 2
Hi Ballardw,.... When GrNumber is missing and the Color is either Red, Orange or Blue, then the BagNumber is 1 since the GrNumber is 1 when the Color is Orange. Hope that helps.
What about GrNumber missing and colors other than Red, Orange or Blue??
Partial rules= partial solution.
You can test for missing values using either the Missing function: If Missing(variable) then <do something>
or use the comparison with a value of . If variable = . then <do something>
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.