BookmarkSubscribeRSS Feed
Xinxin
Obsidian | Level 7

I have data as shown below. I need to add the numbers preceding "mL"  for each ID, into 1 variable, and add the numbers preceding mL/kg per ID into another variable.

I tried removing the spaces but I don't know how many digits the number will be, to count back.

I tried extracting the numeric part but then other numbers like the 9 from the % also gets extracted.

Any help on how to do it in SAS (or Excel) will be appreciated. Thank you!

ID DISPLAY_NAME
1 sodium chloride 0.9 % bolus 2,250 mL
1 sodium chloride 0.9 % bolus 1,000 mL
1 sodium chloride 0.9 % bolus 1,000 mL
2 sodium chloride 0.9 % bolus 500 mL
3 sodium chloride 0.9 % bolus 1,000 mL
3 sodium chloride 0.9 % bolus 250 mL
3 sodium chloride 0.9 % bolus 1,551 mL
4 lactated ringers bolus 1,000 mL
4 sodium chloride 0.9 % bolus 1,000 mL
4 PLASMA-LYTE A bolus 1,000 mL
5 sodium chloride 0.9 % bolus 30 mL/kg/dose
5 sodium chloride 0.9 % bolus 1,000 mL
5 REPLIES 5
mklangley
Lapis Lazuli | Level 10

@Xinxin  There are probably easier ways, but here's one way:

data have;
    input id name $50.;
    datalines;
1 sodium chloride 0.9 % bolus 2,250 mL
1 sodium chloride 0.9 % bolus 1,000 mL
1 sodium chloride 0.9 % bolus 1,000 mL
2 sodium chloride 0.9 % bolus 500 mL
3 sodium chloride 0.9 % bolus 1,000 mL
3 sodium chloride 0.9 % bolus 250 mL
3 sodium chloride 0.9 % bolus 1,551 mL
4 lactated ringers bolus 1,000 mL
4 sodium chloride 0.9 % bolus 1,000 mL
4 PLASMA-LYTE A bolus 1,000 mL
5 sodium chloride 0.9 % bolus 30 mL/kg/dose
5 sodium chloride 0.9 % bolus 1,000 mL
    ;
run;

data want;
    set have;
    reverse_name = upcase(strip(reverse(name)));
    mL = index(reverse_name, 'LM');
    space = find(reverse_name, ' ', mL + 3);
    difference = space - (mL + 3);
    numbers = input(reverse(substr(reverse_name, mL + 3, difference)), comma12.);
    keep id name numbers;
run;

Result:

mklangley_0-1602613869656.png

The code above assumes the desired number is always preceded by a space (" ") and followed by a space and mL (i.e. " mL").

Also, this puts all those numbers into a single variable. Did you need the mL/kg/dose number in a separate variable? It might be easier to have the number in one column, and then have another column for the units (e.g. mL or mL/kg/dose).

vellad
Obsidian | Level 7
data want;
    set have;
    c=max(index(upcase(disp), 'ML/KG'), index(upcase(disp),'ML'));
    if c then res=input(compress(scan(substr(disp,1,c-1),-1, ' '),,'kd'),best.);
run;
Xinxin
Obsidian | Level 7

This also worked....thank you very much!

Xinxin
Obsidian | Level 7
Interesting way to solve it...it worked ...Thank you!
r_behata
Barite | Level 11
data want;
	set have;

/*Regex Pattern*/
/*(?<=bolus\s)(.*)(?=\smL)*/
/*Positive Lookbehind (?<=bolus\s) - Assert that the Regex below matches bolus matches the characters bolus literally*/
/*\s matches any whitespace character */
/*1st Capturing Group (.*)*/
/*.* matches any character (except for line terminators)*/
/*Positive Lookahead (?=\smL) - Assert that the Regex below matches*/
/*\s matches any whitespace character (equal to [\r\n\t\f\v ])*/
/*mL matches the characters mL literally (case sensitive)*/
	pat_id=prxparse('/(?<=bolus\s)(.*)(?=\smL)/');
	call prxsubstr(pat_id,name,pos,len);
	num_=input(substr(name,pos,len),comma8.);
run;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 5 replies
  • 1066 views
  • 3 likes
  • 4 in conversation