How to extract an information from a string and creating new variables

[AMFR]

Hello all,

I have a string variable like below

Blood Transfusion Results 08/24/2018 04:21 Hemoglobin 6.9 gm/dL 08/24/2018 01:29 BG-Lactate 1.33 mmol/L 08/24/2018 07:00 Systolic BP 101 mmHg 08/24/2018 07:00 Heart Rate 95 bpm

I need to create columns for Hemoglobin (value 6.9), Lactate (value 1.33), Systolic BP (101), and Heart rate (95). How can I do this. Please help!!

Also, I was trying to write dataline for this but it was not working out, I was getting truncated data. It will be another question that I will post to get the answer.

Thank you all.

[RW9]

Why do you have a string like that in the first place?  Is this from a file?  Is there some sort of data description for the file?  Can the file be more completely descriptive so post processing not be necessary?  

From what you have there you will need to do some sort of substr(your string,findw("Haemaglobin")+12,next space).

Which is a bit of a mess and prone to issues.

[AMFR]

This is coming from power chart providers use at hospital

[RW9]

So there must be a file definition then, or is this just an empty Notepad file which they type into?

[AMFR]

I honestly don't know about file definition, we have a big data warehouse where I pull data from. Below are codes for 2 of the patients I have in my data, can you please look into this and help me. I am trying with substr but no result

data work.transfuse;

infile datalines delimiter=",";

length Type $100 Result $500;

input Encounter Type $ Result $;

datalines;

1240,Blood Transfusion Reason,Anemia

1240,Packed RBC Transfusion Template,Blood Transfusion Results 08/24/2018 04:21 Hemoglobin 6.9 gm/dL 08/24/2018 01:29 BG-Lactate 1.33 mmol/L 08/24/2018 07:00 Systolic BP 101 mmHg 08/24/2018 07:00 Heart Rate 95 bpm

1240,Transfusion Indications Hemoglobin,Hemoglobin [Hgb] < 8gm/dL

1250,Blood Transfusion Reason,Anemia

1250,Packed RBC Transfusion Template,Blood Transfusion Results 08/22/2018 12:00 Systolic BP 120 mmHg 08/22/2018 12:00 Heart Rate 67 bpm

1250,Transfusion Indications Hemoglobin,Hemoglobin [Hgb] < 8gm/dL

;

run;

proc print data=work.transfuse;

run;

[gamotte · Accepted solution]

Hello,

data have;
infile datalines delimiter=",";
length Type $100 Result $500;
input Encounter Type $ Result $;
datalines;
1240,Blood Transfusion Reason,Anemia
1240,Packed RBC Transfusion Template,Blood Transfusion Results 08/24/2018 04:21 Hemoglobin 6.9 gm/dL 08/24/2018 01:29 BG-Lactate 1.33 mmol/L 08/24/2018 07:00 Systolic BP 101 mmHg 08/24/2018 07:00 Heart Rate 95 bpm
1240,Transfusion Indications Hemoglobin,Hemoglobin [Hgb] < 8gm/dL
1250,Blood Transfusion Reason,Anemia
1250,Packed RBC Transfusion Template,Blood Transfusion Results 08/22/2018 12:00 Systolic BP 120 mmHg 08/22/2018 12:00 Heart Rate 67 bpm
1250,Transfusion Indications Hemoglobin,Hemoglobin [Hgb] < 8gm/dL
;

run;

data want;
set have;
length string2 $200.;

/* We add a field separator */
string2=prxchange("s/([0-9\/]* [0-9]{2}:[0-9]{2})/@/",-1,result);

/* The field separator is used to isolate and output each element */
if result=:"Blood" then do i=2 to countw(string2,'@');
    element=scan(string2,i,'@');
    V1=prxchange("s/([^\d]*).*$/$1/",1,element);
	V2=input(prxchange("s/[^\d]*([\d.]*).*$/$1/",1,element), best.);
	output;
end;

keep Encounter V1 V2;
run;

proc transpose data=want out=want2;
    by Encounter;
    var V2;
    id V1;
run;

Edit: removed tranwrd ans strip as proc transpose transforms values into valid column names.

[AMFR]

Thank you!!