topic Re: Assign number to group in SAS Programming

Assign number to group

kpdoe — Thu, 19 Dec 2019 04:50:05 GMT

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

Re: Assign number to group

andreas_lds — Thu, 19 Dec 2019 06:32:45 GMT

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-data-AKA-generate/ta-p/258712 if you need guidance.

Re: Assign number to group

ed_sas_member — Thu, 19 Dec 2019 08:35:50 GMT

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;

Re: Assign number to group

Ksharp — Thu, 19 Dec 2019 11:48:12 GMT

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;

Re: Assign number to group

Astounding — Thu, 19 Dec 2019 14:21:05 GMT

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

I tried this method but the all the missing group is assi...

kpdoe — Thu, 19 Dec 2019 15:57:30 GMT

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

Re: Assign number to group

kpdoe — Thu, 19 Dec 2019 15:58:41 GMT

Yes it was intentional. I need to do sampling

Re: Assign number to group

kpdoe — Thu, 19 Dec 2019 15:58:55 GMT

Re: Assign number to group

ballardw — Thu, 19 Dec 2019 16:28:52 GMT

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

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