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 |
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.
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;
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;
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 wrote:
Yes it was intentional. I need to do sampling
Then perhaps you should describe the sampling rules instead of an intermediate step.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.