BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sagulolo
Quartz | Level 8

Hi All,

 

Need help for infile coding, my code as below, definitely I have misuse the variable _infile_.

 

 

data have;
    infile datalines truncover dsd;
    format Loc $3. state $10. city $12. postcode $5. code $2. interest $35. second_interest $35.;
    
    input @;
    
    input Loc $ 2-5 state $ 8-18  city $ 19-31 postcode $ 34-39;
    
    if index(_infile_, 'code:') then
        code = _infile_;
    
    else if index(_infile_, 'interest:') then
        interest = _infile_;
    
    else
        second_interest = _infile_;
    
datalines;
 100   Pahang     raub           27600
         code: 1A      interest:  Pine Tree Trail (Bukit Fraser)      
               2nd interest: Allan's Water (Bukit Fraser)
     
     
 500   Selangor   Klang          41000
         code: 2A      interest:  Taman Rakyat Klang
         
 400   Selangor   Subang Jaya    40150
;
run;

Basically the dataline was in 3 style and it can appear 1, 2, 3 and so on, any numbers. I'm believe there a code that infile each of the line depend the dataline style and input into SAS dataset.

 

Appreciated your help and advice

 

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

There are a number of ways to specify how to read data into SAS. Rather than me trying to re-write the documentation, take a look at: https://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a003209907.htm

 

However, quick answer to your questions: using something like @"code" code

searches your datafile for the string "code", then inputs whatever is there. However, it will ignore blanks.

 

If the codes are always in the same position, as in your example, then the following should work:

filename FT15F001 temp;
data have;
  infile FT15F001 truncover end=eof;
  retain Loc state city postcode code interest second_interest;
  informat Loc $3. state $10. city $12. postcode $5. code $2. interest $35. second_interest $35.;
  format Loc $3. state $10. city $12. postcode $5. code $2. interest $35. second_interest $35.;
  input @;
  if index(_infile_, 'code:') gt 0 then do;
    input code 16-17 @"interest: " interest &;
  end;
  else if index(_infile_, '2nd interest:') gt 0 then do;
    input @"2nd interest: " second_interest &;
  end;
  else do;
    if _n_ gt 1 and not missing(loc) then output;
    call missing(of _all_);
    input Loc state city  postcode;
  end;
  if eof then output;
  parmcards;
 100   Pahang     raub           27600
         code: 1A      interest:  Pine Tree Trail (Bukit Fraser)      
               2nd interest: Allan's Water (Bukit Fraser)
 300   Telangor   Klang          41000
         code:         interest:  Something else
         
 500   Selangor   Klang          41000
         code: 2A      interest:  Taman Rakyat Klang
         
 400   Selangor   Subang Jaya    40150
;
run;

Art, CEO, AnalystFinder.com

 

View solution in original post

5 REPLIES 5
sagulolo
Quartz | Level 8

Hi All,

 

re post my code.

 

data have;
	infile datalines truncover dsd;
	format Loc $3. state $10. city $12. postcode $5. code $2. interest $35. second_interest $35.;
	
	input @;
	
	input Loc $ 2-5 state $ 8-18  city $ 19-31 postcode $ 34-39;
	
	if index(_infile_, 'code:') then 
		code = _infile_;
	
	else if index(_infile_, 'interest:') then 
		interest = _infile_;
	
	else
		second_interest = _infile_;
	
datalines;
 100   Pahang     raub           27600
         code: 1A      interest:  Pine Tree Trail (Bukit Fraser)      
               2nd interest: Allan's Water (Bukit Fraser)
     
     
 500   Selangor   Klang          41000
         code: 2A      interest:  Taman Rakyat Klang
         
 400   Selangor   Subang Jaya    40150
;
run;
sagulolo
Quartz | Level 8

Hi All,

 

Apologize for forgot to put my expected output, kindly refer below:-

 

Locstatecitypostcodecodeinterestsecond_interest
100Pahangraub 276001APine Tree Trail (Bukit Fraser)Allan's Water (Bukit Fraser)
       
500Selangor Klang410002ATaman Rakyat Klang 
       
400SelangorSubang Jaya40150   
art297
Opal | Level 21

There may be an easier way, but the following should work:

filename FT15F001 temp;
data have;
  infile FT15F001 truncover end=eof;
  retain Loc state city postcode code interest second_interest;
  informat Loc $3. state $10. city $12. postcode $5. code $2. interest $35. second_interest $35.;
  format Loc $3. state $10. city $12. postcode $5. code $2. interest $35. second_interest $35.;

  input @;
  if index(_infile_, 'code:') gt 0 then do;
    input @"code: " code @"interest: " interest &;
  end;
  else if index(_infile_, '2nd interest:') gt 0 then do;
    input @"2nd interest: " second_interest &;
  end;
  else do;
    if _n_ gt 1 and not missing(loc) then output;
    call missing(of _all_);
    input Loc state city  postcode;
  end;
  if eof then output;
  parmcards;
 100   Pahang     raub           27600
         code: 1A      interest:  Pine Tree Trail (Bukit Fraser)      
               2nd interest: Allan's Water (Bukit Fraser)
     
     
 500   Selangor   Klang          41000
         code: 2A      interest:  Taman Rakyat Klang
         
 400   Selangor   Subang Jaya    40150
;
run;

Art, CEO, AnalystFinder.com

sagulolo
Quartz | Level 8

Hi art297,

 

Thanks for the code and sorry for the delay.

 

It really help me a lot, do you mind to explained this part:-

input @"code: " code @"interest: " interest &;

I have another issue,  data sample per below:-

         code:         interest:  Taman Rakyat Klang

 The field code should be null, but field code is 'in' instead of null. How to fix it if data is null?

 

Best Regards

art297
Opal | Level 21

There are a number of ways to specify how to read data into SAS. Rather than me trying to re-write the documentation, take a look at: https://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a003209907.htm

 

However, quick answer to your questions: using something like @"code" code

searches your datafile for the string "code", then inputs whatever is there. However, it will ignore blanks.

 

If the codes are always in the same position, as in your example, then the following should work:

filename FT15F001 temp;
data have;
  infile FT15F001 truncover end=eof;
  retain Loc state city postcode code interest second_interest;
  informat Loc $3. state $10. city $12. postcode $5. code $2. interest $35. second_interest $35.;
  format Loc $3. state $10. city $12. postcode $5. code $2. interest $35. second_interest $35.;
  input @;
  if index(_infile_, 'code:') gt 0 then do;
    input code 16-17 @"interest: " interest &;
  end;
  else if index(_infile_, '2nd interest:') gt 0 then do;
    input @"2nd interest: " second_interest &;
  end;
  else do;
    if _n_ gt 1 and not missing(loc) then output;
    call missing(of _all_);
    input Loc state city  postcode;
  end;
  if eof then output;
  parmcards;
 100   Pahang     raub           27600
         code: 1A      interest:  Pine Tree Trail (Bukit Fraser)      
               2nd interest: Allan's Water (Bukit Fraser)
 300   Telangor   Klang          41000
         code:         interest:  Something else
         
 500   Selangor   Klang          41000
         code: 2A      interest:  Taman Rakyat Klang
         
 400   Selangor   Subang Jaya    40150
;
run;

Art, CEO, AnalystFinder.com

 

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
  • 5 replies
  • 830 views
  • 1 like
  • 2 in conversation