DATA Step, Macro, Functions and more

free format nonstandard data input

Reply
Occasional Contributor
Posts: 6

free format nonstandard data input

[ Edited ]

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 advanceIMG_2147.JPGIMG_2148.JPG

Contributor
Posts: 29

Re: free format nonstandard data input

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;

Occasional Contributor
Posts: 6

Re: free format nonstandard data input

Posted in reply to ali_jooan

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?

Respected Advisor
Posts: 4,574

Re: free format nonstandard data input

@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.

Super User
Posts: 22,875

Re: free format nonstandard data input

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 advanceIMG_2147.JPGIMG_2148.JPG


 

 

Occasional Contributor
Posts: 6

Re: free format nonstandard data input

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?
Respected Advisor
Posts: 4,574

Re: free format nonstandard data input

@klinz1

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

Occasional Contributor
Posts: 6

Re: free format nonstandard data input

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

Occasional Contributor
Posts: 6

Re: free format nonstandard data input

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 
Super User
Super User
Posts: 7,860

Re: free format nonstandard data input

[ Edited ]

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.)

 

Super User
Posts: 22,875

Re: free format nonstandard data input

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. 

Occasional Contributor
Posts: 6

Re: free format nonstandard data input

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! 

Super User
Posts: 22,875

Re: free format nonstandard data input


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. 

Ask a Question
Discussion stats
  • 12 replies
  • 247 views
  • 2 likes
  • 5 in conversation