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 |
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.
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
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.
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;
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;
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.
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.
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.