topic Re: re: Assign Numbers in SAS Programming

re: Assign Numbers

twildone — Tue, 22 Mar 2022 17:07:04 GMT

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

Re: re: Assign Numbers

ballardw — Tue, 22 Mar 2022 17:56:12 GMT

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:

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

Re: re: Assign Numbers

twildone — Tue, 22 Mar 2022 18:19:38 GMT

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.

Re: re: Assign Numbers

ballardw — Tue, 22 Mar 2022 19:15:55 GMT

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>