Hi everyone,
Pretty new to SAS, so I apologize in advance. I'm trying to import the attached dataset, but I'm having an issue getting it to read the DOB column.
I am using the following code:
DATA employees;
INFILE "C:\Users\dlobsien\Desktop\employees.txt" dsd;
INPUT SSN $11. Name $17. DOB DATE.9 PayGrade $57-60 Salary COMMA10. Position :$27.;
TITLE employee data;
It just shows all the observations under this column as missing data. Everything else reads as I want it to.
I have also tried putting @xx before it as well entering the following with no success:
INFORMAT DOB ANYDTDTE.;
INPUT ... DOB Pay...;
FORMAT DOB DATE9.
If anyone could provide any help it would be greatly appreciated.
Code, tested with your file with UE on a Mac:
data employees;
infile '/folders/myfolders/employees.txt' truncover;
input
@1 ssn $11.
@16 name $30.
@47 dob date9.
@57 paygrade $4.
@62 salary dollar10.
@73 position $30.
;
format
dob e8601da10.
salary dollar12.2
;
run;
You have a fixed-width layout, so you have to use formatted input (as you did), but because of the white space in between you need to use position indicators. You must take care of exact positioning.
Name seems to start at position 16 (11 characters SSN, 4 characters space). Inspect your file with a good editor (notepad++) that shows the cursor position.
Hi @Damon1
The basic approach for fixed width is to specify the start and end column for each variable.
Could you please try this:
DATA employees;
INFILE "C:\Users\dlobsien\Desktop\employees.txt" dsd;
INPUT SSN $ 1-11 Name $ 16-32 DOB:DATE9. PayGrade $57-60 Salary:DOLLAR10. Position $ 73-99;
FORMAT DOB DATE9. Salary DOLLAR10.;
RUN;
Best,
so, this works to get the date in; however, now it gives me both the SSN and Name data under the "positions" column. I have tried setting the length and putting @72 before it in the infile statement.
Ah, I should have used @73 instead, which worked. Thanks so much 🙂
Code, tested with your file with UE on a Mac:
data employees;
infile '/folders/myfolders/employees.txt' truncover;
input
@1 ssn $11.
@16 name $30.
@47 dob date9.
@57 paygrade $4.
@62 salary dollar10.
@73 position $30.
;
format
dob e8601da10.
salary dollar12.2
;
run;
First thing it to fix the INFILE statement. You data is NOT delimited, so remove the DSD option. Also your lines might not have trailing blanks so add the TRUNCOVER option.
INFILE "C:\Users\dlobsien\Desktop\employees.txt" truncover;
Your fields appear to be in fixed columns but there is no way to use an informat when using a range of columns ( 10-20) in the INPUT statement. So you need to read the DOB and SALARY fields differently.
1) You can use formatted mode. Make sure to start in the right column. This is best if the field might be all blanks.
INPUT SSN $ 1-11 Name $ 12-46 @47 DOB DATE9. PayGrade $57-60 @62 Salary COMMA11. Position $ 73-99 ;
2) Use list mode, but make sure there is something in the field. Use a period to indicate a missing value otherwise SAS will scan past the end of the field until it sees something to read. Note that with list mode the width of the INFORMAT specification does not matter, the INPUT statement adjusts the width to match the width of the next thing on the line.
2a) You can either add an INFORMAT statement.
INPUT SSN $ 1-11 Name $ 12-46 DOB PayGrade $57-60 Salary Position $ 73-99 ;
informat dob date. salary comma. ;
2b) Or us the colon modifier in front of the in-line informat specification.
INPUT SSN $ 1-11 Name $ 12-46 DOB :date. PayGrade $57-60 Salary :comma. Position $ 73-99 ;
Here are a few of the lines from your attached file, with a ruler to see the column locations.
1 2 3 4 5 6 7 8 9 ----+----0----+----0----+----0----+----0----+----0----+----0----+----0----+----0----+----0----+ XXX-XX-3484 Edward Rogers 27JUN1989 GR20 $4,653.23 Business Analyst XXX-XX-3305 Steven Price 30JUN1961 GR20 $5,581.94 Systems Engineer I XXX-XX-2575 Donald Graham 07MAR1959 GR26 $15,081.73 Human Resources Director
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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: