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>
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.