topic can't import date data in SAS Programming

can't import date data

Damon1 — Sun, 01 Mar 2020 07:51:17 GMT

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.

Re: can't import date data

Kurt_Bremser — Sun, 01 Mar 2020 08:39:30 GMT

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.

Re: can't import date data

ed_sas_member — Sun, 01 Mar 2020 09:46:51 GMT

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,

Re: can't import date data

Kurt_Bremser — Sun, 01 Mar 2020 10:14:31 GMT

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;

Re: can't import date data

Damon1 — Sun, 01 Mar 2020 18:03:17 GMT

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.

Re: can't import date data

Damon1 — Sun, 01 Mar 2020 18:06:04 GMT

worked perfectly! thanks so much!

Re: can't import date data

Damon1 — Sun, 01 Mar 2020 18:08:29 GMT

Ah, I should have used @73 instead, which worked. Thanks so much 🙂

Re: can't import date data

Tom — Sun, 01 Mar 2020 18:22:12 GMT

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