The SAS Output Delivery System and reporting techniques

Importing Messy Text Data

Accepted Solution Solved
Reply
Regular Contributor
Posts: 220
Accepted Solution

Importing Messy Text Data

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.

Attachment

Accepted Solutions
Solution
‎01-20-2014 06:28 PM
Esteemed Advisor
Posts: 7,285

Re: Importing Messy Text Data

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;

View solution in original post


All Replies
Super Contributor
Posts: 543

Re: Importing Messy Text Data

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.

Regular Contributor
Posts: 220

Re: Importing Messy Text Data

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.

Super User
Super User
Posts: 6,315

Re: Importing Messy Text Data

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

;;;;

Solution
‎01-20-2014 06:28 PM
Esteemed Advisor
Posts: 7,285

Re: Importing Messy Text Data

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 820 views
  • 3 likes
  • 4 in conversation