BookmarkSubscribeRSS Feed
twildone
Pyrite | Level 9

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

 

 

 

 

 

 

 

3 REPLIES 3
ballardw
Super User

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

 

 

 

 

 

 

 


 

twildone
Pyrite | Level 9

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.

ballardw
Super User

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: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 516 views
  • 1 like
  • 2 in conversation