BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Nasser_DRMCP
Lapis Lazuli | Level 10
data have;
input idt_ixn tsp_deb datetime18. top_dec $ ;
informat tsp_deb datetime18.;
format tsp_deb datetime18. ;
datalines ;
123 10NOV2020:10:10:10 0
123 10NOV2020:11:11:11 1
123 10NOV2020:12:12:12 1
456 11NOV2020:10:10:10 0
456 12NOV2020:11:11:11 0
456 13NOV2020:12:12:12 0
;

hello,

I would like tocreate a flag with value "1" only on the first top_dec =1 of the idt_ixn  or on the first idt_ixn when there is no top_dec=1

in this case,

for the group idt_ixn=123, the flag should be 1 only where tsp_deb is 10NOV2020:11:11:11

for the group idt_ixn=456, the flag should be 1 only where tsp_deb is 11NOV2020:10:10:10

thnaks in advance

kind regards

Nass

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

Its a little more tricky than just using FIRST., but this does it:

data have;
input idt_ixn tsp_deb datetime18. top_dec $ ;
informat tsp_deb datetime18.;
format tsp_deb datetime18. ;
datalines ;
123 10NOV2020:10:10:10 0
123 10NOV2020:11:11:11 1
123 10NOV2020:12:12:12 1
456 11NOV2020:10:10:10 0
456 12NOV2020:11:11:11 0
456 13NOV2020:12:12:12 0
;

data want;
merge
  have
  have (
    in=h1
    keep=idt_ixn top_dec
    rename=(top_dec=_top)
    where=(_top = "1")
  )
;
by idt_ixn;
__top = lag(top_dec);
flag = 0;
if first.idt_ixn and not h1 then flag = 1;
if h1 and top_dec = "1" and (first.idt_ixn or __top ne "1") then flag = 1;
drop _top __top;
run;

View solution in original post

2 REPLIES 2
AMSAS
SAS Super FREQ

Check out FIRST. and LAST. Data Step Variables
That's how you can do this
EDIT - Should have read the post more carefully, not as simple as it first appears

Kurt_Bremser
Super User

Its a little more tricky than just using FIRST., but this does it:

data have;
input idt_ixn tsp_deb datetime18. top_dec $ ;
informat tsp_deb datetime18.;
format tsp_deb datetime18. ;
datalines ;
123 10NOV2020:10:10:10 0
123 10NOV2020:11:11:11 1
123 10NOV2020:12:12:12 1
456 11NOV2020:10:10:10 0
456 12NOV2020:11:11:11 0
456 13NOV2020:12:12:12 0
;

data want;
merge
  have
  have (
    in=h1
    keep=idt_ixn top_dec
    rename=(top_dec=_top)
    where=(_top = "1")
  )
;
by idt_ixn;
__top = lag(top_dec);
flag = 0;
if first.idt_ixn and not h1 then flag = 1;
if h1 and top_dec = "1" and (first.idt_ixn or __top ne "1") then flag = 1;
drop _top __top;
run;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 2 replies
  • 320 views
  • 3 likes
  • 3 in conversation