BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hellind
Quartz | Level 8

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.

REFNOACCESSTIMENAME
#26/11/2014 11:10JAMES WARREN
#26/11/2014 11:11JAMES WARREN
S141126-1426/11/2014 11:12JAMES WARREN
#26/11/2014 11:13JAMES WARREN
#26/11/2014 11:14JAMES WARREN
S141126-1526/11/2014 11:15JAMES WARREN
#26/11/2014 11:16JAMES WARREN

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

Like this:

REFNOACCESSTIMENAME
S141126-1426/11/2014 11:10JAMES WARREN
S141126-1426/11/2014 11:11JAMES WARREN
S141126-1426/11/2014 11:12JAMES WARREN
S141126-1526/11/2014 11:13JAMES WARREN
S141126-1526/11/2014 11:14JAMES WARREN
S141126-1526/11/2014 11:15JAMES WARREN
#26/11/2014 11:16JAMES WARREN
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

5 REPLIES 5
EricHoogenboom
Fluorite | Level 6

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
Super User

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
Super User

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
Barite | Level 11

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
Quartz | Level 8

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1096 views
  • 2 likes
  • 5 in conversation