Convert text file data into sas dataset after importing the text file

[mdhtrivedi]

Hello all,

I have a text file that contains data that looks like below:-

ID          Test_1    Test_2    Test_3   Test_4    Test_5
SH101   90.5       88.4         92.3       95.1      90.0
SH102   64.3       64.6         77.4       72.3      71.1
SH103   68.1       69.4         80.6       75.4      70.5
SH104   88.0       77.4         66.2       77.4      67.3
SH105   63.6       70.2         62.1       60.6      72.4

I could import this text file successfully by following query:-

PROC IMPORT OUT=Scores

DATAFILE= "/home/folders/scores.txt"

DBMS=dlm REPLACE ;

RUN;

But I want to convert the data into Sas dataset.

I would be grateful if someone can help me with this issue

Thank you in advance!!

[PeterClemmensen]

Why not just use the data step directly like this?

data MyData;
   infile "YourPathHere\test.txt" firstobs=2;
   input ID $ test_1-test_5;
run;

[mdhtrivedi]

I tried your solution, which does creates an empty dataset with column names but there are no rows in it from the text file. Any clue why it happens this way? Thanks for the reply...

[PeterClemmensen]

what exactly is in your txt file? Do you have column headers in there?

[mdhtrivedi]

Attached is the exact file

[LinusH]

I'm puzzled.
The out option specifies a SAS data set. If that didn't work, share the log.

[mdhtrivedi]

Log is as follows:-

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

70

71 data Scores;

72 infile "/home/mdhtrivedi0/scores.txt" firstobs=5;

73 input ID $ Test_1-Test_5;

74 run;

NOTE: The infile "/home/mdhtrivedi0/scores.txt" is:

Filename=/home/mdhtrivedi0/scores.txt,

Owner Name=mdhtrivedi0,Group Name=oda,

Access Permission=-rw-r--r--,

Last Modified=08Aug2018:08:15:32,

File Size (bytes)=355

NOTE: Invalid data for Test_1 in line 6 1-30.

NOTE: Invalid data for Test_2 in line 7 1-30.

NOTE: Invalid data for Test_3 in line 8 1-30.

NOTE: Invalid data for Test_4 in line 9 1-30.

NOTE: Invalid data for Test_5 in line 10 1-30.

RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0

10 CHAR SH109.66.3.71.1.69.0.64.1.73.1 30

ZONE 543330332303323033230332303323

NUMR 38109966E3971E1969E0964E1973E1

NOTE: Invalid data errors for file '"/home/mdhtrivedi0/scores.txt"' occurred outside the printed range.

NOTE: Increase available buffer lines with the INFILE n= option.

ID=SH10488 Test_1=. Test_2=. Test_3=. Test_4=. Test_5=. _ERROR_=1 _N_=1

NOTE: LOST CARD.

ID=SH11088 Test_1=. Test_2=. Test_3=. Test_4=. Test_5=. _ERROR_=1 _N_=2

NOTE: 7 records were read from the infile "/home/mdhtrivedi0/scores.txt".

The minimum record length was 28.

The maximum record length was 30.

NOTE: SAS went to a new line when INPUT statement reached past the end of a line.

NOTE: The data set WORK.SCORES has 1 observations and 6 variables.

NOTE: DATA statement used (Total process time):

real time 0.05 seconds

user cpu time 0.01 seconds

system cpu time 0.00 seconds

memory 613.65k

OS Memory 26536.00k

Timestamp 09/09/2018 04:36:52 PM

Step Count 18 Switch Count 1

Page Faults 0

Page Reclaims 311

Page Swaps 0

Voluntary Context Switches 14

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 264

75

76 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

88

[Tom]

---

@mdhtrivedi wrote:

Hello all,

I have a text file that contains data that looks like below:-

ID          Test_1    Test_2    Test_3   Test_4    Test_5
SH101   90.5       88.4         92.3       95.1      90.0
SH102   64.3       64.6         77.4       72.3      71.1
SH103   68.1       69.4         80.6       75.4      70.5
SH104   88.0       77.4         66.2       77.4      67.3
SH105   63.6       70.2         62.1       60.6      72.4

I could import this text file successfully by following query:-

PROC IMPORT OUT=Scores

DATAFILE= "/home/folders/scores.txt"

DBMS=dlm REPLACE ;

RUN;

But I want to convert the data into Sas dataset.

I would be grateful if someone can help me with this issue

Thank you in advance!! 

---

If that PROC IMPORT step worked then you have already created the SAS dataset named SCORES in the WORK library.

What is that you want to do differently?

[mdhtrivedi]

Checkout the image. You can see I do not get different columns for "ID", "Test_1", "Test_2", "Test_3", "Test_4"; while using PROC IMPORT. They all are been taken as one column only. This is not how I want. I expect to have different columns of each.

[Reeza · Accepted solution]

Your PROC IMPORT code is incorrect, you didn't specify a delimiter. 

Try one of these instead, which specify a space or tab as a delimiter.

PROC IMPORT OUT=Scores
DATAFILE= "/home/folders/scores.txt"
DBMS=dlm REPLACE ;
DLM= " ";
RUN;


PROC IMPORT OUT=Scores
DATAFILE= "/home/folders/scores.txt"
DBMS=dlm REPLACE ;
DLM= "09"x;
RUN;

In the future please make sure to include significantly more details in your post, specifically, what you're trying to do, the code you used, the log and what is wrong with the approach. You'll get a response much faster.

[mdhtrivedi]

Hi Reeza,

I tried your solution, but both the queries didn't worked. Its still bringing all data into one column. But I ll make sure to add more details in my next post. Thank you for your guidance.

[mdhtrivedi]

Thanks Reeza, I got my solution by adding DBMS as "TAB" instead of delimiter.