import into SAS

[Ronein]

Hello

I am trying to import a very big table into SAS.

The name of the file is  in this format AYYYYMMDD .

In the table there are 57 columns but when I am using the following code I see only 5 columns.

Also the columns names are:

_19000101

_00

_000

_000000000

_000000000000000

and I want the column names will fe : F1 ,F2,.....F57

values in the file are separated by semicolon.

I even cannot know what type of file is it....(for example txt, csv,xlsx and so on)

I just see a photo of file type

My questions:

1- Why do I get only 5 columns while there are 57 columns?

2- How can I control column names (I want columns names F1-F57)

3-How can I know type of file? 

%let YYYYMMDD=20210310; /*Meaning year 2021 month 0 day 10*/

options validvarname=v7;
Filename ttt "/path/ A&YYYYMMDD.";
proc import datafile=ttt out=test dbms=dlm replace;
delimiter=';';
run;

[tarheel13]

You need to look at the file properties in file explorer in Windows to see the file extension.

[Ronein]

There is not information about it.
Name of file: BTT.B2.POTEN.A20210310
Type of file: A20210310
SIZE :1.43 GB
CHARACTER; A

Can you learn anything from it?

[Tom]

---

@Ronein wrote:
There is not information about it.
Name of file: BTT.B2.POTEN.A20210310
Type of file: A20210310
SIZE :1.43 GB
CHARACTER; A

Can you learn anything from it?

---

Yes. We learn what the actual name of the file is.  It does not have an "extension" in the since that people normally mean.  

It might even give some clues about where the file came from.  That type of naming looks a lot like what someone might use on an IBM mainframe computer.

To read a text file just use SAS to look at the beginning of the file.

data _null_;
  infile 'BTT.B2.POTEN.A20210310' obs=5 ;
  input;
  list;
run;

[Ronein]

When I am doing manual proc import via import wizard then I must choose "files of type:All files(*.*), otherwise I cannot see this file.

[RichardDeVen]

What does the first row of the data file look like ? Can you edit the question and add that information ?
In Windows the default line termination is carriage return line feed (CRLF) aka \r\n, but IMPORT will try to guess things.

Does the fifth column in the data file have an embedded newline (\n) alone?
This might occur when exporting Excel data to a CSV and the header cell for a column has a 'two line' value that was made to look that way by typing Shift-Enter in Excel at the line break point.

If so, IMPORT interpreted the header value with the splitting \n as the data line termination and you end up with 5 columns in SAS.

[ballardw]

---

@Ronein wrote:

Hello

I am trying to import a very big table into SAS.

The name of the file is  in this format AYYYYMMDD .

In the table there are 57 columns but when I am using the following code I see only 5 columns.

My questions:

1- Why do I get only 5 columns while there are 57 columns?

2- How can I control column names (I want columns names F1-F57)

3-How can I know type of file? 

1) Show example data, like copy the first 5 lines of data into a text box opened on the forum with the </> icon.

   If you have data that contains single quotes for some of those fields such as

there are measurements like 1.5" in the data; the next field is trapped inside the quotes; that occur with 2"in measurement;

Or at least show what you actually get.

2) Write a data step to read delimited data.

3) If the source can't tell you what the file type is how can we. If you mean the extension then use the file properties-Right click on the icon and then click on "Properties" to see the extension. Windows tracks file "type" or associations based on the extension of the file. If you have no application that is associated with that extension that is the default icon.

[Ronein]

This is the code that is created when I am using import wizard.

My task is to create a code that import the file automatically(with code)  and not with import wizard.

As you can see the path '/usr/local/saswork/SAS_work22F8000037C7_LINX107717A15/#LN00016'

is a temporary path and I cannot use it in the manual code.

Can this code help us to write correct code to import?

Why the code that I wrote is not working? I got only 5 columns whereas there are 57 columns...

DATA DailyPanel_a;
     LENGTH
        F1                 8
        F2                 8
        F3                 8
        F4                 8
        F5                 8
        F6                 8
        F7                 8
        F8               $ 32767
        F9                 8
        F10                8
        F11                8
        F12                8
        F13                8
        F14                8
        F15                8
        F16                8
        F17                8
        F18                8
        F19                8
        F20                8
        F21                8
        F22                8
        F23                8
        F24                8
        F25                8
        F26                8
        F27                8
        F28                8
        F29                8
        F30                8
        F31                8
        F32                8
        F33                8
        F34                8
        F35                8
        F36                8
        F37                8
        F38                8
        F39                8
        F40                8
        F41                8
        F42                8
        F43                8
        F44                8
        F45                8
        F46                8
        F47                8
        F48                8
        F49                8
        F50                8
        F51                8
        F52                8
        F53                8
        F54                8
        F55                8
        F56                8
        F57                8 ;
    FORMAT
        F1               YYMMDD10.
        F2               BEST2.
        F3               BEST3.
        F4               BEST9.
        F5               BEST15.
        F6               BEST15.
        F7               BEST15.
        F8               $CHAR32767.
        F9               BEST15.
        F10              BEST15.
        F11              BEST15.
        F12              BEST15.
        F13              BEST15.
        F14              BEST15.
        F15              BEST15.
        F16              BEST15.
        F17              BEST15.
        F18              BEST15.
        F19              BEST15.
        F20              BEST15.
        F21              BEST15.
        F22              BEST15.
        F23              BEST15.
        F24              BEST15.
        F25              BEST15.
        F26              BEST15.
        F27              BEST15.
        F28              BEST15.
        F29              BEST15.
        F30              BEST15.
        F31              BEST15.
        F32              BEST15.
        F33              BEST15.
        F34              BEST15.
        F35              BEST15.
        F36              BEST15.
        F37              BEST15.
        F38              BEST15.
        F39              BEST15.
        F40              BEST15.
        F41              BEST15.
        F42              BEST15.
        F43              BEST15.
        F44              BEST15.
        F45              BEST15.
        F46              BEST15.
        F47              BEST15.
        F48              BEST15.
        F49              BEST15.
        F50              BEST15.
        F51              BEST15.
        F52              BEST15.
        F53              BEST15.
        F54              BEST15.
        F55              BEST15.
        F56              BEST15.
        F57              BEST15. ;
    INFORMAT
        F1               YYMMDD10.
        F2               BEST2.
        F3               BEST3.
        F4               BEST9.
        F5               BEST15.
        F6               BEST15.
        F7               BEST15.
        F8               $CHAR32767.
        F9               BEST15.
        F10              BEST15.
        F11              BEST15.
        F12              BEST15.
        F13              BEST15.
        F14              BEST15.
        F15              BEST15.
        F16              BEST15.
        F17              BEST15.
        F18              BEST15.
        F19              BEST15.
        F20              BEST15.
        F21              BEST15.
        F22              BEST15.
        F23              BEST15.
        F24              BEST15.
        F25              BEST15.
        F26              BEST15.
        F27              BEST15.
        F28              BEST15.
        F29              BEST15.
        F30              BEST15.
        F31              BEST15.
        F32              BEST15.
        F33              BEST15.
        F34              BEST15.
        F35              BEST15.
        F36              BEST15.
        F37              BEST15.
        F38              BEST15.
        F39              BEST15.
        F40              BEST15.
        F41              BEST15.
        F42              BEST15.
        F43              BEST15.
        F44              BEST15.
        F45              BEST15.
        F46              BEST15.
        F47              BEST15.
        F48              BEST15.
        F49              BEST15.
        F50              BEST15.
        F51              BEST15.
        F52              BEST15.
        F53              BEST15.
        F54              BEST15.
        F55              BEST15.
        F56              BEST15.
        F57              BEST15. ;
    INFILE '/usr/local/saswork/SAS_work22F8000037C7_LINX107717A15/#LN00016'
        LRECL=183290910
        ENCODING="HEBREW"
        TERMSTR=CRLF
        DLM='7F'x
        MISSOVER
        DSD ;
    INPUT
        F1               : ?? YYMMDD8.
        F2               : ?? BEST2.
        F3               : ?? BEST3.
        F4               : ?? BEST9.
        F5               : ?? BEST15.
        F6               : ?? BEST15.
        F7               : ?? BEST15.
        F8               : $CHAR32767.
        F9               : ?? BEST15.
        F10              : ?? BEST15.
        F11              : ?? BEST15.
        F12              : ?? BEST15.
        F13              : ?? BEST15.
        F14              : ?? BEST15.
        F15              : ?? BEST15.
        F16              : ?? BEST15.
        F17              : ?? BEST15.
        F18              : ?? BEST15.
        F19              : ?? BEST15.
        F20              : ?? BEST15.
        F21              : ?? BEST15.
        F22              : ?? BEST15.
        F23              : ?? BEST15.
        F24              : ?? BEST15.
        F25              : ?? BEST15.
        F26              : ?? BEST15.
        F27              : ?? BEST15.
        F28              : ?? BEST15.
        F29              : ?? BEST15.
        F30              : ?? BEST15.
        F31              : ?? BEST15.
        F32              : ?? BEST15.
        F33              : ?? BEST15.
        F34              : ?? BEST15.
        F35              : ?? COMMA15.
        F36              : ?? BEST15.
        F37              : ?? BEST15.
        F38              : ?? BEST15.
        F39              : ?? BEST15.
        F40              : ?? BEST15.
        F41              : ?? BEST15.
        F42              : ?? BEST15.
        F43              : ?? BEST15.
        F44              : ?? BEST15.
        F45              : ?? BEST15.
        F46              : ?? BEST15.
        F47              : ?? BEST15.
        F48              : ?? BEST15.
        F49              : ?? BEST15.
        F50              : ?? BEST15.
        F51              : ?? BEST15.
        F52              : ?? BEST15.
        F53              : ?? BEST15.
        F54              : ?? BEST15.
        F55              : ?? BEST15.
        F56              : ?? BEST15.
        F57              : ?? BEST15. ;
RUN;

 

[Kurt_Bremser]

And, since the EG import wizard has trouble dealing with the file, you will have to move it to the SAS server with the Copy Files task, so that your code can pick it up.

[Kurt_Bremser]

Maxim 3: Know Your Data.

This is equally true for SAS datasets and external data files you need to import.

One of the most handy tools for this is Notepad++. If the simple text mode is not sufficient, switch to hex display, so you can see the line termination and any delimiter characters.

From that you will have a better starting point from which to develop your import code.

If, on opening the file with a text tool, you see legible text organized in lines, copy/paste a few into a window opened with the </> button. DO NOT SKIP THIS.

The main posting window will change the content.

[Sajid01]

Hello
The following statement in your code makes me think that you are having SAS EG on your desktop and connected to SAS Server in a Unix/Linux environment.
" INFILE '/usr/local/saswork/SAS_work22F8000037C7_LINX107717A15/#LN00016'".

In case this has to be done once in a while, It would preferable  to use SAS EG. Detailed programming is not worth the hassle.
In the event this has to done on a regular basis as a scheduled batch process, then the file should be on the Unix server for it to be imported.

Replace the location of the file  in the infile statement with the actual location.

This type of scenario is of common occurrence in the corporate environment.
Lastly in my experience with files of very large sizes (few GB's) , I had situations where I could not open the file in notepad or notepad++..
I would prefer to use the UNIX shell, to have an understanding of the file.in such cases.

[Kurt_Bremser]

Notepad++ can usually do it, not so Notepad.

On UNIX I'd simply use head -5 to get a clue about the contents.

If it is a mainframe file, and if it was downloaded as is, it might be encoded in EBCDIC and not have any line delimiters. But then, as mainframe programmers are involved, there HAS to be good documentation.