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

I have a data set with the variables: participant ID (UUID), day, and event (infx_status). I would like to create a variable (DFI) that signifies when the first infection occurred for each participant. I'm having trouble especially when there is no infection present.

UUIDdayinfx_statusDFI (want)
UU033300
UU0333102
UU0333212
UU0333302
UU0333412
UU0333502
UU0334105
UU0334205
UU0334305
UU0334405
UU0334515
UU033810.
UU033820.
UU033830.
UU033840.
UU033850.

 

Don't know why I am having so much trouble with this but would appreciate some help. Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

I assumed you knew SQL basics, apologies if you don't.

The full code needed:

proc sql; 
 create table WANT as
 select UUID, DAY, INFX_STATUS, min(DAY*ifn(INFX_STATUS, 1, .)) as DIF
 from HAVE
 group by UUID  
 order by UUID, DAY;

 

View solution in original post

7 REPLIES 7
ChrisNZ
Tourmaline | Level 20

Like this?

 select UUID, DAY, INFX_STATUS, min(DAY*ifn(INFX_STATUS, 1, .))
 from HAVE
 group by UUID  
 order by UUID, DAY;
UUID day INFX_STATUS  
UU0333 0 0 2
UU0333 1 0 2
UU0333 2 1 2
UU0333 3 0 2
UU0333 4 1 2
UU0333 5 0 2
UU0334 1 0 5
UU0334 2 0 5
UU0334 3 0 5
UU0334 4 0 5
UU0334 5 1 5
UU0338 1 0 .
UU0338 2 0 .
UU0338 3 0 .
UU0338 4 0 .
UU0338 5 0

.

 

marinalev
Fluorite | Level 6
That's not working. The "min" statement is not changing color and I don't think that would create a new variable, would it? Sorry, I'm new to this.
ChrisNZ
Tourmaline | Level 20

1. Run this in proc sql.

2. The color parser in EG is unreliable. Like the rest of EG. 😉

3. A new column is created, give it a name if you want:   min(..) as VARNAMEOFYOURCHOICE

ChrisNZ
Tourmaline | Level 20

I assumed you knew SQL basics, apologies if you don't.

The full code needed:

proc sql; 
 create table WANT as
 select UUID, DAY, INFX_STATUS, min(DAY*ifn(INFX_STATUS, 1, .)) as DIF
 from HAVE
 group by UUID  
 order by UUID, DAY;

 

marinalev
Fluorite | Level 6
Thank you so much!! Life saver!
mkeintz
PROC Star

The data step approach, if you dataset is sorted by UUID/DAY would be:

 

data want;
  set have (where=(infx_status=1) in=in1)
      have (in=in2);
  by id;
  retain dfi;
  if first.id then dfi=.;
  if in1=1 and dfi=. then dfi=day;
  if in2;
run;

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
ChrisNZ
Tourmaline | Level 20

Or like this:

data WANT;
  set HAVE (where=(INFX_STATUS=1))
      HAVE (in=IN2);
  by UUID;
  retain DFI;
  if first.UUID then DFI=ifn(INFX_STATUS, DAY, .);
  if IN2;
run;

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 2735 views
  • 11 likes
  • 3 in conversation