topic Re: Importing Messy Text Data in ODS and Base Reporting

Importing Messy Text Data

djbateman — Mon, 20 Jan 2014 21:16:18 GMT

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.

Re: Importing Messy Text Data

AncaTilea — Mon, 20 Jan 2014 21:46:03 GMT

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.

Re: Importing Messy Text Data

djbateman — Mon, 20 Jan 2014 21:50:01 GMT

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.

Re: Importing Messy Text Data

Tom — Mon, 20 Jan 2014 22:27:00 GMT

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

;;;;

Re: Importing Messy Text Data

art297 — Mon, 20 Jan 2014 23:28:47 GMT

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;