topic Re: need to extract information out of a text data field in SAS Programming

need to extract information out of a text data field

rykwong — Sat, 08 Apr 2017 18:31:03 GMT

Dear SAS programming community:

I need to search the contents of a variable "outcome_entries" (please see the attached screenshot of a subject). The actual dataset contains several thousand subjects. I need to collect the values in red color. Note that these values are arranged by outcome_name, followed by the outcome values (where 1 means present of that outcome_name and 0 means absent), followed by the year of that event (which can be missing as in the cva), followed by a number of days value. there is set list of these events (un_ang, ami, cva....) that I know the names. I need: 1) each of these events with its corresponding presence or absence (1/0), date, year, and day since values 2) count how many time a specific event happens (e.g. 2 for "heart" and 2 for "ami", but 1 for "pci"

any help is much appreciated, thanks

Raymond

Re: need to extract information out of a text data field

lakshmi_74 — Sat, 08 Apr 2017 19:01:07 GMT

In your dataset, which are not assigned red color there are missing values. So you can collect easily the red mark data by using proc sql and also data step method also. How is the dataset saved, is it in in csv?

Re: need to extract information out of a text data field

rykwong — Sat, 08 Apr 2017 19:09:45 GMT

thanks for the response

the data is stored in a flat csv sheet. the actual data is not colored (please see attached screenshot). I just colored the text that I want for the sake of explaining what I need to extract.

would you have a sas code that shows me how to use proc sql to extract the needed values?

thanks

Re: need to extract information out of a text data field

art297 — Sat, 08 Apr 2017 19:42:37 GMT

Not sure what you want your output file to look like, but the following is one way to read and output multiple entries in such a field within a txt file:

data want (drop=keep_going);
  infile '/folders/myfolders/comments.csv'
    lrecl=32767
    firstobs=2
    dlm='^|';
  informat outcome_name $8.;
  informat outcome_date anydtdte10.;
  format outcome_date date9.;
  keep_going=1;
  do while (keep_going);
    input@'outcome_name:' outcome_name @;
    if missing(outcome_name) then do;
      input;
      keep_going=0;
    end;
    else input @'outcome_value:' outcome_value 
               @'outcome_date:' outcome_date
               @'outcome_date_days_since:' outcome_date_days_since @;
         output;
  end;
run;

Art, CEO, AnalystFinder.com

Re: need to extract information out of a text data field

lakshmi_74 — Sat, 08 Apr 2017 19:43:26 GMT

select outcome_name,outcome_value,outcome_date,outcome_year,outcome_date_days_since
from have ;
select count(*),outcome_value,outcome_name from have group by outcome_name,outcome_value;

Re: need to extract information out of a text data field

Ksharp — Sun, 09 Apr 2017 03:05:28 GMT

data x;
infile 'c:\temp\test.csv' lrecl=327678 dlm=',^:|' dsd firstobs=2 truncover;
input id : $100. x : $200. y : $200.@;
do while(not missing(x));
 output;
 input x : $200. y : $200. @;
end;
run;

Re: need to extract information out of a text data field

ballardw — Mon, 10 Apr 2017 20:46:54 GMT

@rykwong wrote:

the data is stored in a flat csv sheet. the actual data is not colored (please see attached screenshot). I just colored the text that I want for the sake of explaining what I need to extract.

would you have a sas code that shows me how to use proc sql to extract the needed values?

You're not showing an example of a CSV file. You are showing pictures of (most likely) Excel interpretations of a csv. Since the presence or non-presence of line feed or similar characters is important then perhaps you coult attach, as TEXT file format not PNG, a few example rows. Then we would have something we could actually test code against.