topic Re: extract number from string and calculate the mean if the value is in a range in SAS Programming

extract number from string and calculate the mean if the value is in a range

CHL0320 — Wed, 13 Jul 2022 13:47:40 GMT

Dear Community,

I would like to learn how to calculate the mean from string variables some of them contain comments and range.

For example, the data I have is as

ID	hours
1	7-8 hours after play
2	0-1
3	10-12
4	11

And the data wanted is

ID	hours	hours_m
1	7-8 hours after play	7.5
2	0-1	0.5
3	10-12	11
4	11	11

Thank you so much.

Re: extract number from string and calculate the mean if the value is in a range

Kurt_Bremser — Wed, 13 Jul 2022 14:01:47 GMT

Use the COMPRESS function to get rid of anything but digits and hyphens, then count the "words", then calculate or just input:

data have;
infile datalines dlm="09"x dsd truncover;
input ID $ hours :$30.;
datalines;
1	7-8 hours after play
2	0-1
3	10-12
4	11
;

data want;
set have;
string = compress(hours,"-","kd");
if countw(string,"-") = 2
then hours_m = (input(scan(string,2,"-"),best.) + input(scan(string,1,"-"),best.)) / 2;
else hours_m = input(string,best.);
drop string;
run;

Re: extract number from string and calculate the mean if the value is in a range

CHL0320 — Wed, 13 Jul 2022 14:05:12 GMT

Thank you so much. WOW!