Assign number to group

[kpdoe]

Hello, I have an excel file which I create a dataset from and want to assign the group #. How can I achieve the below scenario?

Have:

ID	QTY	GROUP
1
2	0.05	2
3	0.05	2
4
5	0.05	5
6	0.05	5
7
8

Want:

ID	QTY	GROUP
1		1
2	0.05	2
3	0.05	2
4		3
5	0.05	5
6	0.05	5
7		6
8		7

[andreas_lds]

Please post data in usable form, so that we don't have to guess types etc. Have a look at https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... if you need guidance.

[ed_sas_member]

Hi @kpdoe 

Here is some code you can try.

Please check carefully the rules to assign the group number :

- if the group already exists : should be the existing value

- if the group is missing : should be the previous value + 1

- first row : should be 1

Is that correct ?

Best,

data have;
	infile datalines truncover;
	input ID QTY GROUP;
	datalines;
1
2 0.05 2
3 0.05 2
4
5 0.05 5
6 0.05 5
7
8
;
run;

data want;
	set have;
	if nmiss(QTY) = 0 			  then _group = GROUP;
	if nmiss(QTY) = 1 			  then _group + 1;
	if _n_ = 1 and nmiss(QTY) = 1 then _group = 1;
	drop GROUP;
	rename _group = GROUP;
run;

[kpdoe]

I tried this method but the all the missing group is assigned as 1

[Ksharp]

data have;
	infile datalines truncover;
	input ID QTY GROUP;
	datalines;
1
2 0.05 2
3 0.05 2
4
5 0.05 5
6 0.05 5
7
8
;
data want;
 set have;
 retain new_group ;
 if not missing(group) then new_group=group;
  else new_group+1;
run;

[Astounding]

Was skipping from 3 to 5 intentional, or an accident?

What should happen if QTY and/or GROUP changes from one non-missing value to another?

ID	QTY	GROUP
1
2	0.05	2
3	0.77	4
4
5	0.05	5
6	0.05	5
7
8

[kpdoe]

Yes it was intentional. I need to do sampling

[ballardw]

---

@kpdoe wrote:
Yes it was intentional. I need to do sampling

---

Then perhaps you should describe the sampling rules instead of an intermediate step.