Learning SAS? Welcome to the exclusive online community for all SAS learners.

How to import a TXT file and include column names from a separate Excel file

Reply
New Contributor
Posts: 4

How to import a TXT file and include column names from a separate Excel file

I have a really large TXT file that I'm trying to import into SAS University Edition. The file is so big, it won't open in an excel or notepad so I can't edit it. The column names are in a separate Excel file. I used the Import Data process to get the file loaded and I selected GETNAMES=NO so it wouldn't use the first line of the text file as the column names. Now that I have the file imported, it's only giving generic column name values. Any suggestions on how I can merge the two?

 

Here is the code I used. 

 

/* Generated Code (IMPORT) */
/* Source File: itcont.txt */
/* Source Path: /folders/myfolders/sasuser.v94 */
/* Code generated on: 4/29/18, 10:24 AM */

%web_drop_table(WORK.IMPORT1);


FILENAME REFFILE '/folders/myfolders/sasuser.v94/itcont.txt';

PROC IMPORT DATAFILE=REFFILE
 DBMS=DLM
 OUT=WORK.IMPORT1;
 DELIMITER="|";
 GETNAMES=NO;
RUN;

PROC CONTENTS DATA=WORK.IMPORT1; RUN;


%web_open_table(WORK.IMPORT1);

 

Here are the column names:

 

CMTE_ID $ AMNDT_IND $ RPT_TP $ TRANSACTION_PGI $ IMAGE_NUM  TRANSACTION_TP $ ENTITY_TP $ NAME $                CITY $    STATE $ ZIP_CODE EMPLOYER $ OCCUPATION $ TRANSACTION_DT            TRANSACTION_AMT                OTHER_ID $ TRAN_ID $ FILE_NUM           MEMO_CD $ MEMO_TEXT $ SUB_ID

PROC Star
Posts: 635

Re: How to import a TXT file and include column names from a separate Excel file

[ Edited ]

Does your excel file contains column names along with format also? If yes then you can read the excel file and use the var name in Datastep.

 

proc import datafile="D:\SASUniversityEdition\SandBox\Files\CLASS_Vars.xlsx" out=vars
dbms=excel;
getnames=no;
run;
data _null_;
format DLM_File $50.;
  set sashelp.class;
  DLM_File=CATX("|", of _ALL_);
  file "D:\SASUniversityEdition\SandBox\Files\class.txt";
  put DLM_File;
run;
data _null_;
set vars;
call symput("Vars",CATX(" ",of _CHARACTER_));
run;
%PUT &Vars;
data want;
infile "D:\SASUniversityEdition\SandBox\Files\class.txt" dlm="|" missover;
input &vars;
run;

Here my excel file have var names with formats.Capture.PNG

Thanks,
Suryakiran
New Contributor
Posts: 4

Re: How to import a TXT file and include column names from a separate Excel file

Posted in reply to SuryaKiran

It doesn't currently but I can add that and then save to my folder. 

Contributor
Posts: 28

Re: How to import a TXT file and include column names from a separate Excel file

why do you want your column names separetly in excel file. You can directly
define them in DATA STEP.
New Contributor
Posts: 4

Re: How to import a TXT file and include column names from a separate Excel file

I don't want them in a separate excel, that's just the way I was given it. One SUPER large text file and then the header was available from a different Excel file. I'm new to SAS and when I tried to do an INPUT line with the Column names it didn't work. Can you advise how I would write the DATA definition line?

Super User
Super User
Posts: 8,290

Re: How to import a TXT file and include column names from a separate Excel file

Just read the introduction to SAS data steps.

One thing to do so that you can test is to use the OBS=10 option to your INFILE statement to test your data step on just a few lines of the source file.  That way the step will run quickly and you can iterate over many changes until you get the syntax right.  Once you think you have it right remove the OBS= option or change the number to the keyword MAX.

Super User
Super User
Posts: 8,290

Re: How to import a TXT file and include column names from a separate Excel file

[ Edited ]

Why would you use PROC IMPORT to read from a delimited file when you already know what is in it?

data want ;
  infile  '/folders/myfolders/sasuser.v94/itcont.txt' dsd dlm='|' truncover ;
  input CMTE_ID $ AMNDT_IND $ RPT_TP $ TRANSACTION_PGI $
    IMAGE_NUM  TRANSACTION_TP $ ENTITY_TP $ NAME $
    CITY $    STATE $ ZIP_CODE EMPLOYER $ OCCUPATION $
    TRANSACTION_DT            TRANSACTION_AMT                OTHER_ID $
    TRAN_ID $ FILE_NUM           MEMO_CD $ MEMO_TEXT $ SUB_ID
  ;
run;

If some of those character variables are longer than 8 characters then you might want to include an informat specification with a longer width so that SAS will use the width of the informat to set the length of the variable. Just remember to include the colon modifier so that the informat specification doesn't cause SAS to read past the next delimiter.

MEMO_TEXT :$200.

You might need to add INFORMAT and FORMAT statements for your TRANSACTION_DT variable if it actually includes values that look to humans like they represent dates. If not then you might want to read it as a character string instead. I doubt that the file contains numbers that SAS would interpret as a date.

 

Also ZIP_CODE is NOT a number.

New Contributor
Posts: 4

Re: How to import a TXT file and include column names from a separate Excel file

When I tried running it that way before it returned the column names but it didn't pull in the data from the text file. I got columns but 0 rows. I figured it had to do with the fact that I was using SAS University Edition and that it needed to get the data from the shared folder I had set up. I just ran it and got a Physical File Does Not Exist error. 

Super User
Super User
Posts: 8,290

Re: How to import a TXT file and include column names from a separate Excel file

We can't help you find the file on your computer. But you can browse the files in your shared folder using the Server Files and Folders window.  See this description on another thread. https://communities.sas.com/t5/SAS-Analytics-U/Unable-to-create-a-permanent-library-Gives-error-mess...

 

Ask a Question
Discussion stats
  • 8 replies
  • 231 views
  • 1 like
  • 4 in conversation