Help using Base SAS procedures

Help with a certain datastep logic

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 90
Accepted Solution

Help with a certain datastep logic

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

Accepted Solutions
Solution
‎08-06-2015 07:49 AM
Super User
Posts: 6,963

Re: Help with a certain datastep logic

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Occasional Contributor
Posts: 11

Re: Help with a certain datastep logic

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

Solution
‎08-06-2015 07:49 AM
Super User
Posts: 6,963

Re: Help with a certain datastep logic

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 9,687

Re: Help with a certain datastep logic

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;
Valued Guide
Posts: 858

Re: Help with a certain datastep logic

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;

Frequent Contributor
Posts: 90

Re: Help with a certain datastep logic

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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