topic Re: Converting text file (nont-delimited) into SAS file in SAS Procedures

Converting text file (nont-delimited) into SAS file

Ashwini_uci — Sat, 27 Sep 2014 01:07:51 GMT

Hi,

I had created a text file from a SAS dataset that also included positioning of the data elements at certain spaces.

I had used the following code:

filename NDItext 'C:\Users\aerande\Documents\Harbor_UCLA\SouthBaydata\text\SBHW_2014_0051.txt';

data _null_;

file nditext LRECL=100;

set sbhw.SBHW_SAS;

put @1 Last_Name

@21 First_Name

@36 Middle_Initial

@37 SSN

@46 Month

@48 Day

@50 Year

@54 Father_Surname

@72 Age_at_death

@73 Number_of_Age_Units

@75 Sex

@76 Race

@77 Marital_Status

@78 State_of_Residence

@80 State_of_Birth

@82 Control_ID

@92 Age_in_1991

@98 ' ';

run;

Now that text file has 2-3 new variables with the data added into it. and I want to convert that text file with that additional data back into the SAS data file.

I tried importing it and also by using the infile statement; but it did not give me a correct output as the data did not appear under the respective columns.

I would like to know how I convert that text file into the SAS file ? What code do I use to perform this operation?

Any help would be highly appreciated.

Thanks very much!

Ashwini

I am attaching the portion of the original text file; just for reference. The revised text file has 2 -3 more columns with data in it.

Re: Converting text file (nont-delimited) into SAS file

Reeza — Sat, 27 Sep 2014 01:12:19 GMT

Change file/put to infile/input and drop the set statement for starters.

Then how are your new 2-3 variables added in? You can add them tot he input statement at the appropriate location.

You may want to look into the turncover option for your infile statement as well.

Re: Converting text file (nont-delimited) into SAS file

Ashwini_uci — Sat, 27 Sep 2014 01:38:33 GMT

Thanks Reeza! I tried it this way but the data for only 4 variables was correctly placed, Year, Control ID, and Age in 1991. The Last name, firstname, Sex, State of residence are all blank and the control ID is repeated under State of Birth. Not sure why this is happening.. any idea?

THe last field is of 3 spaces and is supposed to be blank.

filename NDItext 'C:\Users\aerande\Documents\Harbor_UCLA\SouthBaydata\text\SBHW_2014_0051.txt';

data sbhw.SBHWtext3_SAS1;

infile nditext LRECL=100 truncover;

input @1 Last_Name

@21 First_Name

@36 Middle_Initial

@37 SSN

@46 Month

@48 Day

@50 Year

@54 Father_Surname

@72 Age_at_death

@73 Number_of_Age_Units

@75 Sex

@76 Race

@77 Marital_Status

@78 State_of_Residence

@80 State_of_Birth

@82 Control_ID

@92 Age_in_1991

@98 Blankfield;

run;

Re: Converting text file (nont-delimited) into SAS file

Reeza — Sat, 27 Sep 2014 02:46:49 GMT

my bad, you'll also need the formats, you can get that from the original dataset used in the set statement.

Proc import it to get an example of what the structure of the import code should look like in the log.

Re: Converting text file (nont-delimited) into SAS file

Ashwini_uci — Sat, 27 Sep 2014 03:03:43 GMT

I tried using proc import earlier, but it doesnot help.

For the item where it asks me to select a delimiter, I am not sure which option i should select. I tried all the options but still it doesnot give me a correct outcome..

I have never worked with formats before, so I am kind of stuck..

It'd be very helpful if you could help me with specifics..

Thanks!

Re: Converting text file (nont-delimited) into SAS file

Reeza — Sat, 27 Sep 2014 03:22:37 GMT

Example 3 on the following link should be another way.

http://www.ats.ucla.edu/stat/sas/modules/input.htm

Re: Converting text file (nont-delimited) into SAS file

Ashwini_uci — Sat, 27 Sep 2014 20:56:06 GMT

Thanks very much Reeza! SO based on that, I constructed my code as below.; But for some reason after the it doesnot show the data under the variable names after this variable- Ninth_condition $ 253-259 .

Not sure what that's happening. Theoutput data shows all the columns but no data underneath these last few columns. It has imported all the records too.

DATA sbhw.NDIresults;

INFILE "C:\Users\aerande\Documents\Harbor_UCLA\SouthBaydata\text\NDICauses.txt" missover lrecl=438;

INPUT

Last_Name $ 1-20

First_Name $ 21-35

Middle_Initial $ 36

SSN $ 37-45

Month $ 46-47

Day $ 48-49

Year 50-53

Father_Surname $ 54-71

Age_at_death 72

Number_of_Age_Units 73-74

Sex $ 75

Race $ 76

Marital_Status $ 77

StResi $ 78-79

StBirth $ 80-81

ID 82-91

Age_1991 92-97

Blank_field 98-100

StDeath $ 101-112

YrDeath 113-116

St_DeathCode $ 117-119

Alias $ 120

DeathCertificateno 121-126

Mon_Death $ 127-128

Day_Death $ 129-130

Year_Death 131-132

FN $ 133-134

MI $ 135

LN $ 136

FS $ 137

LN_FS_userrec $ 138

SSN_1 $ 139-147

Mon_Birth $ 148

Day_Birth $ 149

Year_Birth $ 150-152

Age_death $ 153

Sex1 $ 154

Race1 $ 155

MS $ 156

SR $ 157

SB $ 158

Blank $ 159-164

MatchIndicator $ 165

MatchSeq $ 166-168

PossNDIrecmatches $ 169-171

ProbScore $ 172-176

Classcode $ 177

Statuscode $ 178

Blank1 $ 179

UnderlyingcauseDeath $ 180-183

CauseRecode $ 184-188

CauseRecode1 $ 189-191

Infantcauserecode $ 192-194

Number_Entityaxis 195-196

First_condition $ 197-203

Second_condition $ 204-210

Third_condition $ 211-217

Fourth_condition $ 218-224

Fifth_condition $ 225-231

Sixth_condition $ 232-238

Seventh_condition $ 239-245

Eighth_condition $ 246-252

Ninth_condition $ 253-259

Tenth_condition $ 260-266

Eleventh_condition $ 267-273

Twelvth_condition $ 274-280

Thirteenth_condition $ 281-287

Fourteenth_condition $ 288-294

Fifteenth_condition $ 295-301

Sixteenth_condition $ 302-308

Seventeenth_condition $ 309-315

Eighteenth_condition $ 316-322

Nineteenth_condition $ 323-329

Twentieth_condition $ 330-336

NumberRECaxisconditions $ 337-338

First_condition1 $ 339-343

Second_condition1 $ 344-348

Third_condition1 $ 349-353

Fourth_condition1 $ 354-358

Fifth_condition1 $ 359-363

Sixth_condition1 $ 364-368

Seventh_condition1 $ 369-373

Eighth_condition1 $ 374-378

Ninth_condition1 $ 379-383

Tenth_condition1 $ 384-388

Eleventh_condition1 $ 389-393

Twelvth_condition1 $ 394-398

Thirteenth_condition1 $ 399-403

Fourteenth_condition1 $ 404-408

Fifteenth_condition1 $ 409-413

Sixteenth_condition1 $ 414-418

Seventeenth_condition1 $ 419-423

Eighteenth_condition1 $ 424-428

Nineteenth_condition1 $ 429-433

Twentieth_condition1 $ 434-438

RUN;

Re: Converting text file (nont-delimited) into SAS file

Reeza — Sat, 27 Sep 2014 21:01:30 GMT

I can't really guess without a sample of the file, but I'd try changing missover to turncover and remove the trailing @ at the end of the statement.

Re: Converting text file (nont-delimited) into SAS file

Ashwini_uci — Sat, 27 Sep 2014 21:18:37 GMT

Oh great, the 'truncover' worked!

Thanks very much Reeza! That was a tremendous help. I can meet my deadline tonight

Re: Converting text file (nont-delimited) into SAS file

Reeza — Sat, 27 Sep 2014 21:35:56 GMT

For things like this, test the first, last and middle lines of the text compared to the SAS file just to make sure, and that the number of lines are what you expect...because you never know

Re: Converting text file (nont-delimited) into SAS file

Ashwini_uci — Sat, 27 Sep 2014 22:56:32 GMT

Yes, I did. it all looks right. Thanks a lot again!

Re: Converting text file (nont-delimited) into SAS file

Reeza — Sun, 28 Sep 2014 01:24:23 GMT

Mark question answered