Creating variables by extracting data from a long string

[monali]

Hello,

I have a field in the data table that looks like following. I want to extract patient data such as fullUrl, fgiven name, family name, gender, birth date etc. from this string and create variables. Can someone please assist with this? The source data table has thousands of rows so looking for an efficient way to extract these data. Thanks! 

"{""fullUrl"": ""urn:uuid:XXXXX-XXXX-XXXX-XXX-XXXXXXXXXX"",
""resource"": {""meta"":
{""profile"": [""WEB-URL""]},
""name"": [{""use"": ""usual"", ""text"": ""FTest LTest"", ""given"": [""FTest""],
""family"": ""LTest""}], ""gender"": ""male"", ""address"": [{""use"": ""home"",
""city"": ""TestSt"", ""line"": [""ST ADDRESS""], ""state"": ""XX"",
""country"": ""US"", ""postalCode"": ""99999""}], ""telecom"": [{""use"":
""work"", ""value"": ""1234567890"", ""system"": ""phone""}], ""birthDate"":
""9999-99-99T99:99:99"", ""identifier"": [{""value"": ""1234"", ""system"":
""urn:oid:1.23.456.7.99999999.2.0000""}], ""resourceType"": ""Patient""}}"

[Reeza]

Is it by chance a JSON or XML file? If so, there's libname approaches to parse the data rather than manually parsing it.

[monali]

Thanks. It's a postgres database. The original data feeds are XML files but I am working with Postgres. 

[Shmuel]

You may find interest in next link:

https://groups.google.com/g/comp.soft-sys.sas/c/qqm_12q8hTA?pli=1 

[Shmuel]

Any wanted variable is preceded in the input text with appropriate string followed by a colon ":", the value and closed with any combination of the delimiters: ']},";

without having the input file type, next tested code can be expanded to read all wanted variables:

data a;
  retain line;
  length text $100 line $1000;
  infile cards truncover;
  input text $100. @;
  line = cats(line,text) ;
  if index(text,'}}') then do; output; input; line=' '; end;
  keep line;
cards;
"{""fullUrl"": ""urn:uuid:XXXXX-XXXX-XXXX-XXX-XXXXXXXXXX"",
""resource"": {""meta"":
{""profile"": [""WEB-URL""]},
""name"": [{""use"": ""usual"", ""text"": ""FTest LTest"", ""given"": [""FTest""],
""family"": ""LTest""}], ""gender"": ""male"", ""address"": [{""use"": ""home"",
""city"": ""TestSt"", ""line"": [""ST ADDRESS""], ""state"": ""XX"",
""country"": ""US"", ""postalCode"": ""99999""}], ""telecom"": [{""use"":
""work"", ""value"": ""1234567890"", ""system"": ""phone""}], ""birthDate"":
""9999-99-99T99:99:99"", ""identifier"": [{""value"": ""1234"", ""system"":
""urn:oid:1.23.456.7.99999999.2.0000""}], ""resourceType"": ""Patient""}}"
; run;

%macro chk(str,var);
    %if &var EQ %then %let var = &str;
    ix = index(new_line,"&str"); 
    if ix>0 then &var = scan(substr(new_line,ix),2,':},');
%mend;
data b;
 set a;
     length name $30 family $30 gender $8 birth_date $25 new_line $1000;
     retain name family gender birth_date;
     array ch {*} name family gender birth_date;
     if _N_=1 then call missing(of ch(*));
     new_line = compress(line,'"[]');
     drop line ix new_line;
     
     %chk(given,name);
     %chk(family);
     %chk(gender);
     %chk(birthDate,birth_date);
run;
 

[monali · Accepted solution]

Thank you! This worked!