topic Re: Convert text file data into sas dataset after importing the text file in SAS Programming

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

mdhtrivedi — Sun, 09 Sep 2018 11:04:04 GMT

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!!

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

PeterClemmensen — Sun, 09 Sep 2018 11:33:20 GMT

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;

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

mdhtrivedi — Sun, 09 Sep 2018 12:47:15 GMT

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...

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

PeterClemmensen — Sun, 09 Sep 2018 12:50:14 GMT

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

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

mdhtrivedi — Sun, 09 Sep 2018 12:53:42 GMT

Attached is the exact file

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

LinusH — Sun, 09 Sep 2018 13:54:56 GMT

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

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

Tom — Sun, 09 Sep 2018 14:18:45 GMT

@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?

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

mdhtrivedi — Sun, 09 Sep 2018 16:40:23 GMT

Log is as follows:-

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

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

76 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

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

mdhtrivedi — Sun, 09 Sep 2018 16:52:47 GMT

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.

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

Reeza — Sun, 09 Sep 2018 16:57:54 GMT

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.

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

mdhtrivedi — Sun, 09 Sep 2018 17:08:43 GMT

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.

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

mdhtrivedi — Sun, 09 Sep 2018 17:17:56 GMT

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