topic free format nonstandard data input in SAS Programming

free format nonstandard data input

klinz1 — Tue, 10 Oct 2017 01:57:22 GMT

So I am trying to import a .txt comma dlm file to sas.

I think I have everything correct, except some of the birthdates are coming up as "invalid data" and its skipping over them.. oddly enough I think its all of the october birthdays.. i have provided a couple of attachments of the output and the code i have used.

{this is not real data}

thanks in advance

Re: free format nonstandard data input

ali_jooan — Tue, 10 Oct 2017 00:53:59 GMT

Did you mention informat for the date variable? you can try again with that mmddyy10. ..

data yo;

format DOB date9.;

informat DOB mmddyy10.;

INPUT ... DOB ..;

run;

Re: free format nonstandard data input

Reeza — Tue, 10 Oct 2017 01:04:43 GMT

@IF it's comma delimited why are you using @/+1 to denote positions? You're mixing read in styles.

Try removing the positional parameters.

I would suggest using PROC IMPORT to read the data first, examine the log and then customize it as needed.

Please don't post images, post your code and log as text.

@klinz1 wrote:

So I am trying to import a .txt comma dlm file to sas.

I think I have everything correct, except some of the birthdates are coming up as "invalid data" and its skipping over them.. oddly enough I think its all of the october birthdays.. i have provided a couple of attachments of the output and the code i have used.

{this is not real data}

thanks in advance

Re: free format nonstandard data input

klinz1 — Tue, 10 Oct 2017 01:06:59 GMT

in my "format" line i have the birthdate mmddyy10.

I found that if i remove the +1 from in front of birthdate all birthdates are imported, however, then the rows with salaries that are 6 numerics the birthdate moves to the hiredate and the hiredate moves to the termdate in the output..

suggestions?

Re: free format nonstandard data input

Patrick — Tue, 10 Oct 2017 01:12:41 GMT

@klinz1

As @Reeza wrote: You've got a comma separated source file which you can read in easily using LIST input style only. Don't use any other styles and though remove all your @ and + which move the input pointer.

Re: free format nonstandard data input

klinz1 — Tue, 10 Oct 2017 01:16:02 GMT

my bad.. I've never used this forum before..

so the code that i am now using looks like:

data employee;
infile ".txt." dlm="," dsd truncover;

input id gender $1. salary dollar7. birthdate : mmddyy10. hiredate : mmddyy10. termdate : mmddyy10.
maritalstat $1. +1 numdependents $1. +1 promostatus $1.;
format salary dollar7. birthdate mmddyy10. hiredate mmddyy10. termdate mmddyy10.;
run;

it is importing all birthdates. however, now when the salary is 6 numerics instead of 5, like the first 2 lines in the txt photo, the birthdate is showing up as the hiredate, and the hiredate is showing us as the termdate..

suggestions?

Re: free format nonstandard data input

Patrick — Tue, 10 Oct 2017 01:20:19 GMT

@klinz1

Why are you using dollar7. if your source data is not in this format?

Re: free format nonstandard data input

klinz1 — Tue, 10 Oct 2017 01:28:42 GMT

if i make it "salary 6." or "salary 10-15" only the salaries that are 6 characters are imported.

Re: free format nonstandard data input

Reeza — Tue, 10 Oct 2017 01:30:52 GMT

I sort of commend you for trying to understand your code and fix it. At the same time, the solution recommended is still valid. Did you attempt that method?

>I would suggest using PROC IMPORT to read the data first, examine the log and then customize it as needed.

To elaborate some more, PROC IMPORT generates a data step for the import of text files. This is essentially the code you're trying to write.

Re: free format nonstandard data input

klinz1 — Tue, 10 Oct 2017 01:32:10 GMT

the code i currently have input is:

data employee;
infile ".txt." dlm="," dsd truncover;

input id gender $ salary 6. birthdate : mmddyy10. hiredate : mmddyy10. termdate : mmddyy10.
marital tat $ numdependents promostatus ;
format birthdate mmddyy10. hiredate mmddyy10. termdate mmddyy10.;
run;

the result did not output any salaries that were 5 characters. and the rows with salaries that are 6 numerics are moved over one, i.e. birthdate is showing up as hiredate and hiredate is showing up as termdate.

other than that its correct.. lol

Re: free format nonstandard data input

klinz1 — Tue, 10 Oct 2017 01:34:31 GMT

lol, thank you.

i will try that. however, this is for an assignment and we were explicitly told NOT to use proc import.. unfortunately.

Thanks for all your help, if you have a suggestion on how to fix the salary problem I'm now having that would be appreciated as well!

Re: free format nonstandard data input

Reeza — Tue, 10 Oct 2017 02:17:55 GMT

@klinz1 wrote:

lol, thank you.

i will try that. however, this is for an assignment and we were explicitly told NOT to use proc import.. unfortunately.

Thanks for all your help, if you have a suggestion on how to fix the salary problem I'm now having that would be appreciated as well!

This means you don't put a PROC IMPORT in your answer, it doesn't mean you can't use it to see what code you need.

1. Don't use the : operator within the INPUT, unless you're 100% sure you understand what it's doing.

2. Create an INFORMAT statement BEFORE your INPUT statement.

3. Create a FORMAT statement

4. Make your INPUT statement only include the list of variables, everything else is prespecified. To see an example, see the code from a PROC IMPORT.

Re: free format nonstandard data input

Tom — Tue, 10 Oct 2017 04:25:43 GMT

Do not use formatted input to read a delimited list, instead use list mode. Read the manual on the INPUT statement and it will explain the different input styles.

Define your variables before you use them in other statements. That includes before you use them in FORMAT, INFORMAT and INPUT statements. Then you are sure that your variables will be defined as you want them. It also makes it easier to use list mode input style since you do not have to worry about adding informats or column locations to the INPUT to help SAS guess how you wanted the variable defined.

data employee;
  infile "myfile.txt" dlm="," dsd truncover;
  length id 8 gender $1 salary birthdate hiredate termdate 8
         maritalstat numdependents promostatus $1
  ;
  input id -- promostatus ;
  informat salary dollar7. birthdate hiredate termdate mmddyy10.;
  format salary dollar7. birthdate hiredate termdate mmddyy10.;
run;

Note is does not matter where you put the FORMAT and INFORMAT statements as those are attributes of the variables that are set when the data step is compiled and not when it executes. (But note that if do put them BEFORE you have defined the variables via a LENGTH, ATTRIB or setting an existing dataset then SAS will add the variables to the dataset based on the format you have attached since they will be the first place you have used the variable. And it will impact the order that the variables are in the dataset.)