Read in SAS with two lines end and start at different positions

0
votes

I have two lines of observations to read in SAS. It is a comma-delimited data set. My code is as below:

DATA SASweek1.industry;
  INFILE "&Dirdata.Assignment1_Q6_data.txt" DLM="," DSD termstr=crlf TRUNCOVER;
  LENGTH Company $ 15;
  INPUT Company $ State $ Expense COMMA9. ;  
  FORMAT Expense DOLLAR9.;
  *INFORMAT Expense DOLLAR10.;
RUN; * not ready;

The raw data set looks like this:

enter image description here

I can print out the first line of observations well, but the last "0" will go to the first position of the second line, becoming "0Lee's..". Any suggestions would be highly appreciated!!

1
sas
What output do you get if you remove these lines FORMAT Expense DOLLAR9.; *INFORMAT Expense DOLLAR10.; – Hackerman
Put your data as text please, To work with it I would have to type it in. You need to use an INFORMAT (before INPUT) to specify the comma format and it can be read correctly. There's a full explanation and workaround details in this post here: communities.sas.com/t5/SAS-Programming/data-step-error/m-p/… – Reeza
On second thought that may not be close enough. – Reeza
rit0-my.sharepoint.com/:i:/g/personal/jc3992_rit_edu/… The output – Chen

1 Answers

1
votes

It is just doing what you told it to do. You told it to read exactly 9 characters.

Normally you should not use formatted input mode with delimited data. You prevent that by either adding the : (colon) prefix in front of the informat specification in the INPUT statement or removing the informat specification completely and using an INFORMAT statement to let SAS know what informat to use.

But your data is NOT properly delimited because the last field contains the delimiter, but the value is not enclosed in quotes. So the commas make it look like two values instead of one. The real solution is to fix the process that created the file to create a valid delimited file. It needs to quote the values with commas in them, or remove the commas from the numbers, or use a delimiter character that does not appear in the data.

Fortunately since it is the last field on the line you CAN use formatted input to read just that field. Since you are using the TRUNCOVER option just set the width of the informat in the INPUT statement to the maximum.

DATA SASweek1.industry;
  INFILE "&Dirdata.Assignment1_Q6_data.txt" DLM="," DSD termstr=crlf TRUNCOVER;
  LENGTH Company $15 State $15 Expense 8;
  INPUT Company State Expense COMMA32. ;  
  FORMAT Expense DOLLAR9.;
RUN;