DATA Step, Macro, Functions and more

need to extract information out of a text data field

Reply
Contributor
Posts: 39

need to extract information out of a text data field

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


Screen Shot 2017-04-08 at 2.20.11 PM.png
Contributor
Posts: 57

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

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?
Contributor
Posts: 39

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

Posted in reply to lakshmi_74

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 

 


Screen Shot 2017-04-08 at 3.06.37 PM.png
Super User
Posts: 11,343

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


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.

PROC Star
Posts: 7,477

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

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

 

Contributor
Posts: 57

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

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;
Super User
Posts: 10,035

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

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;
Ask a Question
Discussion stats
  • 6 replies
  • 152 views
  • 0 likes
  • 5 in conversation