topic Re: Creating variables by extracting data from a long string in SAS Programming

Creating variables by extracting data from a long string

monali — Fri, 16 Oct 2020 17:22:48 GMT

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""}}"

Re: Creating variables by extracting data from a long string

Reeza — Fri, 16 Oct 2020 17:51:24 GMT

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

Re: Creating variables by extracting data from a long string

Shmuel — Fri, 16 Oct 2020 19:30:33 GMT

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;

Re: Creating variables by extracting data from a long string

monali — Fri, 16 Oct 2020 19:56:19 GMT

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

Re: Creating variables by extracting data from a long string

Shmuel — Fri, 16 Oct 2020 20:02:14 GMT

You may find interest in next link:

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

Re: Creating variables by extracting data from a long string

monali — Fri, 16 Oct 2020 20:13:38 GMT

Thank you! This worked!