topic Re: Sequence numner in SAS Programming

Sequence numner

nv1950 — Wed, 10 Jul 2019 10:00:16 GMT

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

Re: Sequence numner

PeterClemmensen — Wed, 10 Jul 2019 10:15:19 GMT

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;

Re: Sequence numner

PeterClemmensen — Wed, 10 Jul 2019 10:17:52 GMT

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;

Re: Sequence numner

nv1950 — Wed, 10 Jul 2019 10:46:40 GMT

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

Re: Sequence numner

KachiM — Wed, 10 Jul 2019 11:47:40 GMT

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;