topic Re: How to take first three different reocrds values in one observation in SAS Programming

How to take first three different reocrds values in one observation

raja777pharma — Sat, 01 Feb 2025 07:23:01 GMT

Hi Team,

I am try to create new var from below data , for each subject select first three different EXODSEL values and concatenate with -, based on sort by USUBJID , EXSTDTC,EXDOSEL.

How to do this one.

Thank you,

Raja.

Re: How to take first three different reocrds values in one observation

Ksharp — Sat, 01 Feb 2025 07:37:59 GMT

That would be better if you could post your dataset by sas code , NOT just a picture.

Nobody would like to type it by hand for you if you need answer quickly.

data have;
input id EXDOSEL $;
cards;
1 1
1 1
1 1
1 1
2 .
3 .
5 0.3
5 1.0
5 1.5
5 1.5
5 1.5
6 .
6 .
7 0.3
7 1.0
7 1.5
7 1.6
7 1.8
;
data want;
 do until(last.id);
  set have;
  by id;
  length new_var $ 80;
  if not findw(new_var,EXDOSEL,'-','it') and countw(new_var,'-') <3 then new_var=catx('-',new_var,EXDOSEL);
 end;
 do until(last.id);
  set have;
  by id;
  output;
 end;
 run;

Re: How to take first three different reocrds values in one observation

raja777pharma — Sat, 01 Feb 2025 11:50:53 GMT

HI Ksharp,

Thank you for code , but code not working properly , may be i didn't write properly

the data is :

USUBJID	EXSTDTC	EXDOSEL
1001	2022-10-11T12:53	1
1001	2022-10-18T14:35	1
1001	2022-10-25T16:18	1
1001	2022-11-02T15:40	1
1002	2022-12-24T10:00	0
1003
1005
1007	2023-12-06T12:10	0.3
1007	2023-12-12T15:15	1
1007	2023-12-27T15:14	1.5
1007	2024-01-03T12:10	1.5
1007	2024-02-04T15:10	1.5
2002	2023-12-06T12:10	0.3
2002	2023-12-12T15:15	1
2002	2023-12-27T15:14	1.5
2002	2024-01-03T12:10	1.5

I want data for each subject one row , to create newvar as different levels of EXDOSEL in to newvar , if no different EXODSEL then will take one value entire subject as below want data

USUBJID	NewVAR
1001	1
1002	0
1003	-
1005	-
1007	0.3-1-1.5
2002	0.3-1-1.5

Re: How to take first three different reocrds values in one observation

quickbluefish — Sat, 01 Feb 2025 13:10:01 GMT

I think if just want one row per person, you just need to modify @Ksharp 's code to only output the last row for every person. So:

Remove this part:

 do until(last.id);
  set have;
  by id;
  output;
 end;

... and replace with:

if last.id then output;

And of course just change the variable names to match your data (replace ID with SUBJID, etc.)

Also, as KSharp said, when you include data or code here, it's best to just paste it into the box that you get when you click the

button.

Re: How to take first three different reocrds values in one observation

Tom — Sat, 01 Feb 2025 18:15:21 GMT

If you want it only output one observation per BY group then just remove the second DO loop.

data want;
  do until(last.id);
    set have;
    by id;
    length new_var $ 80;
    if not findw(new_var,EXDOSEL,'-','it') and countw(new_var,'-') <3 then new_var=catx('-',new_var,EXDOSEL);
  end;
  keep id new_var;
run;

Now the implied output at the end of the data step will write one observation per by group.