04-29-2018 11:10 AM
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 */
FILENAME REFFILE '/folders/myfolders/sasuser.v94/itcont.txt';
PROC IMPORT DATAFILE=REFFILE
PROC CONTENTS DATA=WORK.IMPORT1; RUN;
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
04-29-2018 11:50 AM - edited 04-29-2018 11:52 AM
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.
04-29-2018 12:07 PM
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?
04-29-2018 12:19 PM
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.
04-29-2018 11:56 AM - edited 04-29-2018 11:59 AM
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.
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.
04-29-2018 12:18 PM
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.
04-29-2018 12:22 PM
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...