Here is my example dataset:
fruit apple
fruit apple
fruit orange
fruit orange
fruit orange
vegetable celery
vegetable broccoli
meat beef
meat chicken
How do I create a third column, that identifies the group number in column 1?
The resulting dataset should look like this
fruit apple 1
fruit apple 1
fruit orange 1
fruit orange 1
fruit orange 1
vegetable celery 2
vegetable broccoli 2
meat beef 3
meat chicken 3
data want;
set have;
by col1 notsorted;
retain subgroup 0;
if first.col1 then subgroup + 1;
run;
You might consider sorting by col1 first, so you can omit the notsorted option and make sure that a certain subgroup does not receive more than one number.
data want;
set have;
by col1 notsorted;
retain subgroup 0;
if first.col1 then subgroup + 1;
run;
You might consider sorting by col1 first, so you can omit the notsorted option and make sure that a certain subgroup does not receive more than one number.
Thanks!
I have other string columns in my RETAIN statement. It doesn't seem to work if I put those columns in front of subgroup 0. Anyway around this?
@schoi wrote:
Thanks!
I have other string columns in my RETAIN statement. It doesn't seem to work if I put those columns in front of subgroup 0. Anyway around this?
In order for in-depth help, post your code (with log if code fails with ERROR or WARNING) and some example data to test the code against. See @PeterClemmensen's example data step for how to post data.
data have;
input food$ type$;
datalines;
fruit apple
fruit apple
fruit orange
fruit orange
fruit orange
vegetable celery
vegetable broccoli
meat beef
meat chicken
;
proc sort data=have;
by food;
run;
data want;
set have;
by food;
if first.food then groupnum+1;
run;
Thanks everyone!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.