@rykwong wrote:
thanks
do you mind sharing an example how to use Data step to read in a large txt file? The txt file is extremely large with thousands of columns of variables so I do not know how to use infile statement to define the large number of variables.
thanks
Thousands of columns? That means the resulting dataset will have thousands of variables. PROC IMPORT cannot guess the names of so many variables because it will only read the first 32K bytes from the header line. If you must GUESS how to name the variables use a tool like %csv2ds() that can read whole header line.
Note that having thousands of observations does not matter when deciding how to read the file. Just whether you will have enough space to store the resulting dataset.
From your SAS log it does not look like the file is delimited with spaces as your told PROC IMPORT to use.
Instead I suspect the delimiter is | character:
EMPI|EPIC_PMRN|MRN_Type|MRN|Date|Procedure_Name|Code_Type|Code|Procedure_Flag|Quantity|Provider|Clinic
|Hospital|Inpatient_Outpatient|Encounter_number truncated to
EMPI_EPIC_PMRN_MRN_Type_MRN_Date.
And I doubt that you have thousands of columns since the longest line is only 1,311 bytes long.
The minimum record length was 109.
The maximum record length was 1311.
Try adding this statement to your PROC IMPORT step.
delimiter='|';
Note that you can write your own data step to define the variables and read them in. Just define the variables (set the type and for character variables the maximum storage they will need) then read them in. If any of them need special instructions for reading attach an informat. If any of them need special instructions for displaying attach a format. Most variables will need neither attached. The main exception are DATE, TIME and DATETIME values.
So your program might look something like:
data procedure;
infile "C:\Users\rk64\Desktop\cardreport_diag_procedure\RK64_20240207_103523_Prc.txt"
dsd dlm='|' truncover firstobs=2
;
length EMPI $20 EPIC_PMRN $20 MRN_Type $10 MRN $15 Date 8
Procedure_Name $40 Code_Type $10 Code $10
Procedure_Flag $8 Quantity 8
Provider $20 Clinic $20 Hospital $20
Inpatient_Outpatient $10 Encounter_number 8
;
input EMPI -- Encounter_number;
informat date date.;
format date date9.;
run;
The DATA statement says what dataset to create.
The INFILE statement says which file to read, and how to read it. In this case lines delimited by | starting with the second line.
The LENGTH statement defines the variable types and storage length (use 8 from numeric variables since SAS stores them as 64 bit floating point numbers).
The INPUT statement says what variables to read from the line. This one uses a positional variable list. So make sure to define the variables in the order they appear in the file.
The FORMAT and INFORMAT statement attach those to the DATE variable so SAS will know how to read the text in the file into a date value and how to display that date value in a way that humans will understand.
But you probably need to look at the file more carefully to decide if it has 12, 39 or some other number of variables. An easy way to look at the file is to use a data _null_ step with the LIST statement to dump the first few lines to the SAS log.
data _null_;
infile "C:\Users\rk64\Desktop\cardreport_diag_procedure\RK64_20240207_103523_Prc.txt"
obs=5 lrecl=1000000
;
input;
list;
run;
Then you can check if there are the same number of names on the first line as there are values on lines 2 to 5. And begin to get a sense for what types of values they have and what might be a reasonable storage length to use for each one.
