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;
You need to look at the file properties in file explorer in Windows to see the file extension.
@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 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.
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;
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.
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.
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.
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: