How do I modify my input statement to read data with multiple embedded blanks?

[Viktor_Radirs]

/*  code used in SAS Studio  */

Data tempin3;	
format MSRP dollar15.;
input Make $ Model $ Origin $ MSRP;                         /* input #1 */
*input Make $ Model & $32 Origin $ MSRP;                         /* input #2 **/
*input Make $1-5  Model & $   Origin $ MSRP;                       /* input #3 ***/
datalines;
Acura	MDX	Asia	$36,945
Acura	RSX Type S 2dr	Asia	$23,820
Acura	TSX 4dr	Asia	$26,990
Acura	TL 4dr	Asia	$33,195
Acura	3.5 RL 4dr	Asia	$43,755
Acura	3.5 RL w/Navigation 4dr	Asia	$46,100
Acura	NSX coupe 2dr manual S	Asia	$89,765
Audi	A4 1.8T 4dr	Europe	$25,940
;
run;

/* log for code with Input # 1: */
 
1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 68         
 69         Data tempin3;
 70         format MSRP dollar15.;
 71         input Make $ Model $ Origin $ MSRP;           /* Input # 1*/                
 72         *input Make $ Model & $32 Origin $ MSRP;                         
 73         *input Make $1-5  Model & $   Origin $ MSRP;                       
 74         datalines;
 
 NOTE: Invalid data for MSRP in line 76 16-16.
 RULE:      ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                     
 
 76  CHAR   Acura.RSX Type S 2dr.Asia.$23,820                                               
     ZONE   46776055525776252367047660233233322222222222222222222222222222222222222222222222
     NUMR   13521923804905030242913919423C82000000000000000000000000000000000000000000000000
 MSRP=. Make=AcuraMD Model=AcuraRS Origin=Type _ERROR_=1 _N_=1
 NOTE: Invalid data for MSRP in line 78 10-25.
 
 78  CHAR   Acura.TL 4dr.Asia.$33,195                                                       
     ZONE   46776054236704766023323332222222222222222222222222222222222222222222222222222222
     NUMR   1352194C0442913919433C1950000000000000000000000000000000000000000000000000000000
 MSRP=. Make=AcuraTS Model=4drAsia Origin=AcuraTL _ERROR_=1 _N_=2
 NOTE: Invalid data for MSRP in line 80 1-9.
 
 80  CHAR   Acura.3.5 RL w/Navigation 4dr.Asia.$46,100                                      
     ZONE   46776032325427246766676662367047660233233322222222222222222222222222222222222222
     NUMR   1352193E502C07FE1697149FE0442913919446C10000000000000000000000000000000000000000
 MSRP=. Make=Acura3. Model=RL Origin=4drAsia _ERROR_=1 _N_=3
 NOTE: Invalid data for MSRP in line 81 21-26.
 
 81  CHAR   Acura.NSX coupe 2dr manual S.Asia.$89,765                                       
     ZONE   46776045526677623672666766250476602332333222222222222222222222222222222222222222
     NUMR   135219E3803F50502420D1E51C03913919489C765000000000000000000000000000000000000000
 MSRP=. Make=AcuraNS Model=coupe Origin=2dr _ERROR_=1 _N_=4
 NOTE: LOST CARD.
 83         ;
 MSRP=. Make=AudiA4 Model=1.8T Origin=4drEuro _ERROR_=1 _N_=5
 NOTE: SAS went to a new line when INPUT statement reached past the end of a line.
 NOTE: The data set WORK.TEMPIN3 has 4 observations and 4 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.01 seconds
       memory              668.71k
       OS Memory           29356.00k
       Timestamp           09/16/2021 12:57:47 PM
       Step Count                        73  Switch Count  2
       Page Faults                       0
       Page Reclaims                     99
       Page Swaps                        0
       Voluntary Context Switches        10
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           272
       
 
 83         ;
 84         run;
 85         
 86         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 96  

---------------------------------------------------
/* log for code with Input # 2: */

1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 68         
 69         Data tempin3;
 70         format MSRP dollar15.;
 71         *input Make $ Model $ Origin $ MSRP;                        
 72         input Make $ Model & $32 Origin $ MSRP;                      /* Input # 2**/   
 73         *input Make $1-5  Model & $   Origin $ MSRP;                       
 74         datalines;
 
 NOTE: Invalid data for MSRP in line 77 11-26.
 RULE:      ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                     
 
 77  CHAR   Acura.TSX 4dr.Asia.$26,990                                                      
     ZONE   46776055523670476602332333222222222222222222222222222222222222222222222222222222
     NUMR   1352194380442913919426C990000000000000000000000000000000000000000000000000000000
 MSRP=. Make=AcuraMD Model=A Origin=AcuraTS _ERROR_=1 _N_=1
 NOTE: Invalid data for MSRP in line 80 11-12.
 
 80  CHAR   Acura.3.5 RL w/Navigation 4dr.Asia.$46,100                                      
     ZONE   46776032325427246766676662367047660233233322222222222222222222222222222222222222
     NUMR   1352193E502C07FE1697149FE0442913919446C10000000000000000000000000000000000000000
 MSRP=. Make=AcuraTL Model=A Origin=Acura3. _ERROR_=1 _N_=2
 NOTE: Invalid data for MSRP in line 82 9-12.
 
 82  CHAR   Audi.A4 1.8T 4dr.Europe.$25,940                                                 
     ZONE   47660432323523670477676023323332222222222222222222222222222222222222222222222222
     NUMR   154991401E8404429552F059425C9400000000000000000000000000000000000000000000000000
 MSRP=. Make=AcuraNS Model=i Origin=AudiA4 _ERROR_=1 _N_=3
 NOTE: SAS went to a new line when INPUT statement reached past the end of a line.
 NOTE: The data set WORK.TEMPIN3 has 3 observations and 4 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              668.59k
       OS Memory           29100.00k
       Timestamp           09/16/2021 01:02:07 PM
       Step Count                        79  Switch Count  2
       Page Faults                       0
       Page Reclaims                     134
       Page Swaps                        0
       Voluntary Context Switches        9
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 
 83         ;
 84         run;
 85         
 86         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 96       
----------------------------------------------------------------------------------------
/* log for code with Input # 3: */

1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 68         
 69         Data tempin3;
 70         format MSRP dollar15.;
 71         *input Make $ Model $ Origin $ MSRP;                         
 72         *input Make $ Model & $32 Origin $ MSRP;                         
 73         input Make $1-5  Model & $   Origin $ MSRP;               /* Input #3***/                     
 74         datalines;
 
 NOTE: Invalid data for MSRP in line 76 11-14.
 RULE:      ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                     
 
 76  CHAR   Acura.RSX Type S 2dr.Asia.$23,820                                               
     ZONE   46776055525776252367047660233233322222222222222222222222222222222222222222222222
     NUMR   13521923804905030242913919423C82000000000000000000000000000000000000000000000000
 MSRP=. Make=Acura Model=MDXAsi Origin=AcuraRS _ERROR_=1 _N_=1
 NOTE: Invalid data for MSRP in line 78 10-25.
 
 78  CHAR   Acura.TL 4dr.Asia.$33,195                                                       
     ZONE   46776054236704766023323332222222222222222222222222222222222222222222222222222222
     NUMR   1352194C0442913919433C1950000000000000000000000000000000000000000000000000000000
 MSRP=. Make=Acura Model=TSX 4dr Origin=AcuraTL _ERROR_=1 _N_=2
 NOTE: Invalid data for MSRP in line 80 11-12.
 
 80  CHAR   Acura.3.5 RL w/Navigation 4dr.Asia.$46,100                                      
     ZONE   46776032325427246766676662367047660233233322222222222222222222222222222222222222
     NUMR   1352193E502C07FE1697149FE0442913919446C10000000000000000000000000000000000000000
 MSRP=. Make=Acura Model=3.5 RL Origin=Acura3. _ERROR_=1 _N_=3
 NOTE: Invalid data for MSRP in line 82 9-12.
 
 82  CHAR   Audi.A4 1.8T 4dr.Europe.$25,940                                                 
     ZONE   47660432323523670477676023323332222222222222222222222222222222222222222222222222
     NUMR   154991401E8404429552F059425C9400000000000000000000000000000000000000000000000000
 MSRP=. Make=Acura Model=NSX cou Origin=AudiA4 _ERROR_=1 _N_=4
 NOTE: SAS went to a new line when INPUT statement reached past the end of a line.
 NOTE: The data set WORK.TEMPIN3 has 4 observations and 4 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              675.81k
       OS Memory           28844.00k
       Timestamp           09/16/2021 01:03:51 PM
       Step Count                        85  Switch Count  2
       Page Faults                       0
       Page Reclaims                     118
       Page Swaps                        0
       Voluntary Context Switches        12
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 
 83         ;
 84         run;
 85         
 86         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 96  

[Viktor_Radirs]

In my earlier post I have shown the code I tried with three types of input statements and error log for each of them. 

How do I correct my input statement to read the data? I think the problem may be due to one of the column having multiple embedded blanks.

Thanks for your help

[sbxkoenk]

Hello,

You can work with a delimiter, like the vertical bar.

data want;
infile datalines delimiter='|';
input ...;
datalines;
... | ... | ...
;
run;

You can also specify the column has to be read between position 22 and 35 for example, like

input Team $ 22-35 

You can also use Column Pointer Controls.
Embedded blanks are not a problem at all. See the doc on the input statement.

Koen

[Viktor_Radirs]

Thanks Koen. I am trying to read a tab delimited file. I will use the method suggested by Tom.

[ballardw]

One question to get started: How do you want "multiple embedded blanks" treated?

[Viktor_Radirs]

Thanks for your time. Please see my reply to Tom to see if it answers your question. I am basically trying to read a tab delimited file by data step instead of proc import (which works just fine).

[Tom]

Did you intend to have tabs in your data?  Or are you using one of those text editors that replaces mulitple spaces with tabs to save a few bytes of disk space?

If you intended to have tabs between the fields then tell SAS that in the INFILE statement.  But don't do it with in-line data.  Put the lines of data into a file because normal SAS Display Manager interface will automatically convert the tabs in your program into spaces before sending the code to be run.  It is only with interfaces like SAS/Studio that the tabs embedded in in-line data are passed onto SAS as actual tab characters.

data tempin3;	
  filefile 'myfile.txt' dsd dlm='09'x truncover ;
  length Make $13 Model $40 MRSP 8 ;
  informat MRSP comma.;
  format MSRP dollar15.;
  input Make Model MRSP ;
run;

[Viktor_Radirs]

Thanks. I am trying to read tab delimited file through a data step. Since it  didn't work the way I wanted, I took the first few lines of data and tried to read with Datalines so I can understand what is going wrong.

Tom's suggested code worked except the column

revised code:
data tempin3;	
 Infile '"/home/u58977675/EPG1V2/output/carsnew.txt"' dsd dlm='09'x truncover ;
  length Make $13 Model $40 Origin $6 MSRP 8 ;  
  informat MSRP comma.;  
  format MSRP dollar15.;  
  input Make Model Origin MSRP ;
 run;
error log:
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 68         
 69         data tempin3;
 70          Infile '"/home/u58977675/EPG1V2/output/carsnew.txt"' dsd dlm='09'x truncover ;
 71           length Make $13 Model $40 Origin $6 MSRP 8 ;
 72           informat MSRP comma.;
 73           format MSRP dollar15.;
 74           input Make Model Origin MSRP ;
 75          run;
 
 NOTE: The infile '"/home/u58977675/EPG1V2/output/carsnew.txt"' is:
       Filename=/home/u58977675/EPG1V2/output/carsnew.txt,
       Owner Name=u58977675,Group Name=oda,
       Access Permission=-rw-r--r--,
       Last Modified=31Aug2021:08:34:32,
       File Size (bytes)=15526
 
 NOTE: Invalid data for MSRP in line 1 19-22.
 RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                      
 
 1   CHAR  Make.Model.Origin.MSRP 22
     ZONE  4666046666047666604555
     NUMR  D1B59DF45C9F2979E9D320
 Make=Make Model=Model Origin=Origin MSRP=. _ERROR_=1 _N_=1
 NOTE: 429 records were read from the infile '"/home/u58977675/EPG1V2/output/carsnew.txt"'.
       The minimum record length was 21.
       The maximum record length was 59.
 NOTE: The data set WORK.TEMPIN3 has 429 observations and 4 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.11 seconds
       user cpu time       0.01 seconds
       system cpu time     0.00 seconds
       memory              762.65k
       OS Memory           24484.00k
       Timestamp           09/16/2021 02:32:42 PM
       Step Count                        78  Switch Count  2
       Page Faults                       0
       Page Reclaims                     132
       Page Swaps                        0
       Voluntary Context Switches        20
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 
 76         
 77         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 87   

title line produced error. I think I need to adjust the input statement for that but I don't know how.

Tom, can you please look at the revised code and error log and suggest how I need to changes that. Thanks so much.

[Tom · Accepted solution]

It is complaining because you tried to read the header row as if it was a data row.  So the string MSRP is not a valid number.

Use the FIRSTOBS=2 option on the INFILE statement to skip the first line of the file.

[Viktor_Radirs]

Thanks so much, Tom! Your suggestion worked great. I got the results I wanted and also learned a  better way to write INFILE statement. Appreciate it.

data tempin3;	
 Infile '"/home/u58977675/EPG1V2/output/carsnew.txt"' dsd dlm='09'x truncover firstobs=2;
  length Make $13 Model $40 Origin $6 MSRP 8 ;  
  informat MSRP comma.;  
  format MSRP dollar15.;  
  input Make Model Origin MSRP ;
 run;

Just for the benefit of anyone who wants to see the fully corrected code, I have attached it also.