Hi all
Im having trouble creating a dataset from a txt.file, where * and $ is amongst my variables.
The data:
(ii);
A123 4Mar1989 8,6,00
***************
A037 23Jun1957 21,450
**************
M015 19Sep1977$17,500
***********
Code so far:
data ii; input DEPT $1. @1 ID $ BIRTHDAY :date9. +(-5) YEAR :8. SALARY comma10.0; datalines; A123 4Mar1989 8,6,00
***************
A037 23Jun1957 21,450
**************
M015 19Sep1977$17,500
*********** ; run;
However this loads the *** and the $ as missing characters.
The first record should be:
ID = "A123", DEPT = "A", BIRTHDAY = 10655, YEAR = 1989, SALARY = 8600
Any suggestions?
Here is my solution
data ii;
/* Input the record into memory (_INFILE_) but do not release it */
input @;
/* Remove the $ sign from the record */
_infile_ = translate(_infile_,' ','$');
/* Read the record again and skip the ***** line using the slash sign (/) */
input ID $ BirthDay :date9. Salary comma10.0 /;
/* Create derived variables */
Dept = substr(ID,1,1);
Year = year(BirthDay);
format BirthDay date9. Salary comma10.0 ;
datalines;
A123 4Mar1989 8,6,00
***************
A037 23Jun1957 21,450
**************
M015 19Sep1977$17,500
***********
;
run;
I am assuming that every other line is a line with only ***************
Here is my solution
data ii;
/* Input the record into memory (_INFILE_) but do not release it */
input @;
/* Remove the $ sign from the record */
_infile_ = translate(_infile_,' ','$');
/* Read the record again and skip the ***** line using the slash sign (/) */
input ID $ BirthDay :date9. Salary comma10.0 /;
/* Create derived variables */
Dept = substr(ID,1,1);
Year = year(BirthDay);
format BirthDay date9. Salary comma10.0 ;
datalines;
A123 4Mar1989 8,6,00
***************
A037 23Jun1957 21,450
**************
M015 19Sep1977$17,500
***********
;
run;
I am assuming that every other line is a line with only ***************
And if the occurence of the ****** lines is irregular, here a slight variation of @MichaelLarsen's code:
data ii;
/* Input the record into memory (_INFILE_) but do not release it */
input @;
if not index(_infile_,'****');
/* Remove the $ sign from the record */
_infile_ = translate(_infile_,' ','$');
/* Read the record again */
input ID $ BirthDay :date9. Salary comma10.0;
/* Create derived variables */
Dept = substr(ID,1,1);
Year = year(BirthDay);
format BirthDay date9. Salary comma10.0 ;
datalines;
A123 4Mar1989 8,6,00
***************
A037 23Jun1957 21,450
**************
M015 19Sep1977$17,500
***********
;
run;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.