topic Re: infile conditonally by dataline style in SAS Programming

infile conditonally by dataline style

sagulolo — Tue, 21 Nov 2017 14:22:57 GMT

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

Re: infile conditonally by dataline style

sagulolo — Tue, 21 Nov 2017 14:25:42 GMT

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;

Re: infile conditonally by dataline style

sagulolo — Wed, 22 Nov 2017 02:41:05 GMT

Hi All,

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

Loc	state	city	postcode	code	interest	second_interest
100	Pahang	raub	27600	1A	Pine Tree Trail (Bukit Fraser)	Allan's Water (Bukit Fraser)

500	Selangor	Klang	41000	2A	Taman Rakyat Klang

400	Selangor	Subang Jaya	40150

Re: infile conditonally by dataline style

art297 — Wed, 22 Nov 2017 03:19:19 GMT

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

Re: infile conditonally by dataline style

sagulolo — Wed, 29 Nov 2017 14:55:47 GMT

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

Re: infile conditonally by dataline style

art297 — Wed, 29 Nov 2017 15:40:02 GMT

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