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

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.

 

 

raja777pharma_0-1738394573648.png

 

 

Thank you,

Raja.

 

1 ACCEPTED SOLUTION

Accepted Solutions
quickbluefish
Barite | Level 11

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 

 

quickbluefish_0-1738415354076.png

button.

 

 

View solution in original post

4 REPLIES 4
Ksharp
Super User

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;
 
raja777pharma
Fluorite | Level 6

HI Ksharp,

 

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

 

the data is :

 

USUBJIDEXSTDTCEXDOSEL
10012022-10-11T12:531
10012022-10-18T14:351
10012022-10-25T16:181
10012022-11-02T15:401
10022022-12-24T10:000
1003  
1005  
10072023-12-06T12:100.3
10072023-12-12T15:151
10072023-12-27T15:141.5
10072024-01-03T12:101.5
10072024-02-04T15:101.5
20022023-12-06T12:100.3
20022023-12-12T15:151
20022023-12-27T15:141.5
20022024-01-03T12:101.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 

 

USUBJIDNewVAR
10011
10020
1003-
1005-
10070.3-1-1.5
20020.3-1-1.5
quickbluefish
Barite | Level 11

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 

 

quickbluefish_0-1738415354076.png

button.

 

 

Tom
Super User Tom
Super User

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.

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

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
  • 4 replies
  • 911 views
  • 0 likes
  • 4 in conversation