BookmarkSubscribeRSS Feed
klinz1
Calcite | Level 5

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

12 REPLIES 12
ali_jooan
Obsidian | Level 7

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;

klinz1
Calcite | Level 5

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?

Patrick
Opal | Level 21

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

Reeza
Super User

@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


 

 

klinz1
Calcite | Level 5

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?
Patrick
Opal | Level 21

@klinz1

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

klinz1
Calcite | Level 5

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

klinz1
Calcite | Level 5

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 
Tom
Super User Tom
Super User

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

 

Reeza
Super User

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. 

klinz1
Calcite | Level 5

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! 

Reeza
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 1921 views
  • 2 likes
  • 5 in conversation