DATA Step, Macro, Functions and more

infile conditonally by dataline style

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

infile conditonally by dataline style

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

 


Accepted Solutions
Solution
‎11-29-2017 11:35 AM
PROC Star
Posts: 8,104

Re: infile conditonally by dataline style

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


All Replies
Contributor
Posts: 60

Re: infile conditonally by dataline style

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;
Contributor
Posts: 60

Re: infile conditonally by dataline style

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   
PROC Star
Posts: 8,104

Re: infile conditonally by dataline style

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

Contributor
Posts: 60

Re: infile conditonally by dataline style

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

Solution
‎11-29-2017 11:35 AM
PROC Star
Posts: 8,104

Re: infile conditonally by dataline style

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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 116 views
  • 1 like
  • 2 in conversation