Importing Messy Text Data

[djbateman]

Does anyone have any idea how I could get the attached txt file into SAS?  I attempted to change it to a delimited file using a colon, but there are too many colons in the variable values.  The format of the file is the variable name in the first column and the variable value in the second column.

[AncaTilea]

The file you've attached has information like this:

Study 1:

  Title:                    A Multicenter Study of Pseudoephedrine for the Temporary Relief of Nasal Congestion in Children With the Common Cold

  Recruitment:              Recruiting

  Study Results:            No Results Available

  Conditions:               Nasal Congestion Associated With the Common Cold

  Interventions:            Drug: pseudoephedrine hydrochloride 30 mg tablets|Drug: Placebo tablets

  URL:                      http://ClinicalTrials.gov/show/NCT01744106

How precisely would you like this read into SAS?

Anca.

[djbateman]

If possible, it would be nice to read in everything into 2 columns.  I would then transpose the data so that I have 7 variables (study, title, recruitment, results, conditions, interventions, & URL) and 13618 observations.  Is it possible?  If not, I can deal with the best we can get.  The Conditions variable is the most important.

[Tom]

Looks simple to me.

Read the line and split it at the first colon. If all of the blocks have the same number of lines then it is even easier, otherwise you can use the STUDY lines to mark where new records begin.

Here is an example of reading the values into VAR/VALUE pairs, but you could easily adapt it to read them into 6 variables instead.

data want ;

  *infile 'study_fields.txt' lrecl=2000 truncover ;

  infile cards truncover;

  length study $20 var $13 value $1024 ;

  input @29 value $1024.;

  if _infile_=' ' then delete;

  if _infile_=:'Study' then do;

     study=scan(_infile_,1,':') ;

     retain study ;

     delete;

  end;

  var = left(scan(_infile_,1,':'));

cards4;

Study 1:

  Title:                    A Multicenter Study of Pseudoephedrine for the Temporary Relief of Nasal Congestion in Children With the Common Cold

  Recruitment:              Recruiting

  Study Results:            No Results Available

  Conditions:               Nasal Congestion Associated With the Common Cold

  Interventions:            Drug: pseudoephedrine hydrochloride 30 mg tablets|Drug: Placebo tablets

  URL:                      http://ClinicalTrials.gov/show/NCT01744106

Study 2:

  Title:                    First-line Treatment of Patients With Stage IV Nonsquamous Non-Small Cell Lung Cancer With Necitumumab (IMC-11F8) and Pemetrexed-Cisplatin

  Recruitment:              Active, not recruiting

  Study Results:            No Results Available

  Conditions:               Non Small Cell Lung Cancer

  Interventions:            Drug: pemetrexed|Drug: cisplatin|Biological: IMC-11F8

  URL:                      http://ClinicalTrials.gov/show/NCT00982111

;;;;

[art297 · Accepted solution]

Tom,

You are DEFINITELY better with messy data then I am!

However, your code didn`t work correctly for the first study, thus I modified it as follows:

data want;

  infile "c:\art\study_fields.txt" lrecl=32767 truncover;

  length var $13 value $1024 ;

  input @29 value $1024.;

  retain study ;

  if _infile_=' ' then delete;

  else if _n_ eq 1 or _infile_=:'Study' then do;

     _infile_=compress(_infile_,,'kd');

     study=input(_infile_,12.) ;

     delete;

  end;

  else var = left(scan(_infile_,1,':'));

run;