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
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
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;
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 |
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
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.