Help with a certain datastep logic

[hellind]

I have this data-set, NAME is the primary key. AccessTime is the order sequence.

REF NO = "#" means customer did not go to the end of a step-by-step webform, instead JAMES clicked saved.

REF NO not equal "#" means customer clicked submit at the end of the steps, and system assigned a reference number.

Then the 4th data row indicates same customer started another application.

REFNO	ACCESSTIME	NAME
#	26/11/2014 11:10	JAMES WARREN
#	26/11/2014 11:11	JAMES WARREN
S141126-14	26/11/2014 11:12	JAMES WARREN
#	26/11/2014 11:13	JAMES WARREN
#	26/11/2014 11:14	JAMES WARREN
S141126-15	26/11/2014 11:15	JAMES WARREN
#	26/11/2014 11:16	JAMES WARREN

I want the REFNO fields populated with the next REFNO ordered by ACCESSTIME

Like this:

REFNO	ACCESSTIME	NAME
S141126-14	26/11/2014 11:10	JAMES WARREN
S141126-14	26/11/2014 11:11	JAMES WARREN
S141126-14	26/11/2014 11:12	JAMES WARREN
S141126-15	26/11/2014 11:13	JAMES WARREN
S141126-15	26/11/2014 11:14	JAMES WARREN
S141126-15	26/11/2014 11:15	JAMES WARREN
#	26/11/2014 11:16	JAMES WARREN

[EricHoogenboom]

Hallo Hellind,

If you are sure that the refno is present every 4th observation you can merge the dataset by itself.

Here is an example (unfortunately I cannot paste in this box, so I keep it simple):

v stands for value (refno in your situation).

data have;

   input id v;

1 .

1 .

1 .

1 4

2 .

2 .

2 .

2 8

run;

data want;

   merge have (drop=v) test (firstobs=4 keep=v rename=(v=_v));

   retain v;

   if _v NE . then v = _v;

   drop _v;

run;

Hope this helps!

Eric

[Kurt_Bremser · Accepted solution]

Sort by name and descending accesstime. Then do

data want (drop=oldrefno);

set have (rename=(refno=oldrefno));

by name;

retain refno;

if first.name or oldrefno ne '#' then refno = oldrefno;

run;

Then recreate the original sort order.

[Ksharp]

Code: Program

data have;
infile cards expandtabs;
input (REFNO ACCESSTIME NAME) ( & $20.);
cards;
# 26/11/2014 11:10 JAMES WARREN
# 26/11/2014 11:11 JAMES WARREN
S141126-14 26/11/2014 11:12 JAMES WARREN
# 26/11/2014 11:13 JAMES WARREN
# 26/11/2014 11:14 JAMES WARREN
S141126-15 26/11/2014 11:15 JAMES WARREN
# 26/11/2014 11:16 JAMES WARREN
;
run;
data want;
if 0 then set have;
 do until(REFNO ne '#' or last);
  set have end=last;
 end;
 _REFNO=REFNO;
 do until(REFNO ne '#' or last);
  set have end=last; 
  output;
 end;
drop REFNO;
run;

[Steelers_In_DC]

Here's a solution:

data have;

    infile cards dsd;

    length refno accesstime name $25.;

   input REFNO$    ACCESSTIME$    NAME$;

   cards;

#,26/11/2014 11:10,JAMES WARREN

#,26/11/2014 11:11,JAMES WARREN

S141126-14,26/11/2014 11:12,JAMES WARREN

#,26/11/2014 11:13,JAMES WARREN

#,26/11/2014 11:14,JAMES WARREN

S141126-15,26/11/2014 11:15,JAMES WARREN

#,26/11/2014 11:16,JAMES WARREN

;

proc sort data=have;by descending accesstime;

data want;

set have;

by descending accesstime;

retain _refno;

if refno ne '#' then _refno = refno;

if refno = '#' and not missing(_refno) then refno = _refno;

drop _:;

run;

proc sort data=want;by accesstime;

[hellind]

Thanks, I used your option for my coding.

This data is from web application. There can be many users, and each time a user click SAVE, a record with REFNO="#" is saved. But the moment the user click SUBMIT, the web application interface with the backend system to assign a reference number.

I am yet to try other suggestions, but thanks everyone.