Hi,
I need to add sequence number based on 3 columns
data test;
input division$ group$ file$;
datalines;
a t1 1
b m2 5
a t1 1
b m2 5
a t2 3
a t2 3
a t2 4
b m2 6
a t1 2
b m1 6
b m1 6
a t2 4
;
proc sort data=test;
by division group file;
run;
data want;
set test;
by division group;
Seq+1;
if first.group then Seq=1;
run;
I want to see the sequence like in the last column, based on the group and file
Welcome to the SAS Community 🙂
Use file instead of group like this
proc sort data=test;
by division group file;
run;
data want;
set test;
by division group file;
Seq+1;
if first.file then Seq=1;
run;
Just for fun, you can use a hash object to achieve the same thing without sorting your data if you want to preserve the original order of data.
data want;
if _n_=1 then do;
dcl hash h();
h.defineKey ("division", "group", "file");
h.defineData ("Seq");
h.defineDone ();
end;
set test;
if h.find() ne 0 then Seq = 1;
else Seq + 1;
h.replace();
run;
Thank you. This worked 🙂
Initially I have the data for the same record in 2 separate columns. So I am adding the sequence number first, then use proc sql min case to bring the records from row 1 and row to
min(case when Seq = 1 then data end)as data1,
min(case when Seq = 2 then data end)as data2
Not sure if there is any easier or quicker way, below is my final output, where the data moved from 2 separate rows into one
Another way.
data test; input division$ group$ file$; datalines; a t1 1 b m2 5 a t1 1 b m2 5 a t2 3 a t2 3 a t2 4 b m2 6 a t1 2 b m1 6 b m1 6 a t2 4 ; run; proc sort data = test; by division group file; run; data need; set test; retain pstr; length str pstr $4; str = cats(division,group,file); if pstr = str then seq + 1; else if pstr ^= str then do; seq = 1; pstr = str; end; run;
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.