topic Re: Data not showing when importing delimited file in New SAS User

Data not showing when importing delimited file

annie_1 — Mon, 22 Jul 2019 17:18:03 GMT

Hi,

I am trying to get data from a dat file to show in sas. There are multiple columns in the dat file but i only want three. Below is the code I used but when I open the resulting file in SAS only the first column is showing data. Any suggestions would be great. Thank you.

data WORK.THIRTY;
infile 'D:\XX\P_02\30\New folder\LBDS2023-20190630-005517Z-DataLog_User.dat' delimiter='09'x DSD truncover lrecl=32767 firstobs=2 ;
input DATE :$10. TIME :$15. H20 :$20.;
run;

Re: Data not showing when importing delimited file

Tom — Mon, 22 Jul 2019 17:37:22 GMT

Please show a couple of the lines from the file. If you cannot figure out how to open a text file and copy a couple of lines just use this code to have SAS show the lines in the LOG. Then copy the lines from the log.

data _null_;
infile 'D:\XX\P_02\30\New folder\LBDS2023-20190630-005517Z-DataLog_User.dat'  obs=3;
input;
list;
run;

To paste the text into the forum click on the Insert Code button one the menu bar. Looks like {i}. Then paste the lines into the window that pops-up.

Re: Data not showing when importing delimited file

Reeza — Mon, 22 Jul 2019 17:42:30 GMT

You cannot selectively import columns unless you have a fixed width file. You have to read all the data and then filter out the columns after the fact. Try reading in all the columns and then keeping only the ones you need.

@annie_1 wrote:

Hi,

I am trying to get data from a dat file to show in sas. There are multiple columns in the dat file but i only want three. Below is the code I used but when I open the resulting file in SAS only the first column is showing data. Any suggestions would be great. Thank you.

data WORK.THIRTY;
infile 'D:\XX\P_02\30\New folder\LBDS2023-20190630-005517Z-DataLog_User.dat' delimiter='09'x DSD truncover lrecl=32767 firstobs=2 ;
input DATE :$10. TIME :$15. H20 :$20.;
run;

A

Re: Data not showing when importing delimited file

ballardw — Mon, 22 Jul 2019 19:37:35 GMT

As @Reeza says, can't really "skip" columns in delimited data. But you can read unwanted data into a variable and then drop that variable.

Suppose the Time variable is actually the 5th column, that means you would want to ignore the 2nd, 3rd and 4th columns.

This is one way to do that:

data WORK.THIRTY;
   infile 'D:\XX\P_02\30\New folder\LBDS2023-20190630-005517Z-DataLog_User.dat' delimiter='09'x DSD truncover lrecl=32767 firstobs=2 ;
   informat dummy $1.;
   input DATE :$10. dummy dummy dummy TIME :$15. H20 :$20.;
   drop dummy;
run;

Place the dummy variable in the column position of any column you don't want to read. You also only need to include up to the last column desired.

The informat for dummy is one character so that it will "read" almost anything and not generate an error whereas a numeric could well have issues with the character values.

Re: Data not showing when importing delimited file

annie_1 — Mon, 22 Jul 2019 20:12:33 GMT

@ballardw

@Reeza

@Tom

I used the below code to import the file

"proc import datafile='D:\XX\P_02\30\New folder\LBDS2023-20190630-005517Z-DataLog_User.dat' dbms=dlm out= work.THIRTY replace;
delimiter='09'X;
getnames=yes;
guessingrows=6000;
run;"

but when I ran it no data showed and even the headers did not show completely (there are about 20 columns). Below is a snip of what the output looked like. So I used F4 to recall the generated DATA step. Which I edited to the code that was in the first post. I also attached a snip of what the dat file looks like in note pad

Re: Data not showing when importing delimited file

Tom — Mon, 22 Jul 2019 20:15:39 GMT

Run the code I posted before to dump the first 3 lines of the file into the log. THen copy the lines from the log. From the start of the data step to the end of the notes after the step. Do NOT post a photograph of your screen. Post the actual lines of text from the SAS log.

Re: Data not showing when importing delimited file

Reeza — Mon, 22 Jul 2019 20:55:29 GMT

I don't think you have a tab delimted file, I think you have what's called a fixed format file and you need to use a data step to read it.

Example #2 here:
https://stats.idre.ucla.edu/sas/modules/inputting-data-into-sas/