Need advice writing SAS programs to read datas

[Takumi]

4. The following table lists 3 personnel records of a company.
Staff ID Birthday Monthly Salary
A123 4 March 1989 8,600
A037 23 June 1957 21,450
M015 19 September 1976 17,500

Staff id number has the format that it starts with a character which is followed by three digits. The first character is the abbreviation of the department (“A” for Accounting, and “M” for Marketing, etc.). Suppose the data are as given in (i) and (ii). Write SAS programs to read the data in (i) and (ii) to create a temporary SAS data file, called PERSONNEL, which contains five variables, namely ID, DEPT, BIRTHDAY, YEAR and SALARY. They should be stored in the above specified order. YEAR is the year of birth. Variables ID and DEPT are character variables and all other variables are numeric. The first record should have ID = “A123”, DEPT = “A”, BIRTHDAY = 10655, YEAR = 1989, SALARY = 8600. You are not allowed to use assignment statement to create any of the above five variables.

This is the data for (ii) mentioned above:

A123 4Mar1989 8,6,00
***************
A037 23Jun1957 21,450
**************
M015 19Sep1976$17,500
***********

This is what I tried:
1) input ID $ 4. +(-3)DEPT $ 1. +2 BIRTHDAY 9 DATE9. +(-4) YEAR 4.
+2 SALARY comma6.;

2) data PERSONNEL;
input @1 ID $ 4. @1 DEPT $ 1. @7 BIRTHDAY DATE9. @12 YEAR 4.
@19 SALARY comma6.;
CARDS;
A123 4Mar1989 . 8,60000
A037 23Jun1957 21,45000a
M015 19Sep1976 . 17,50000
run;
proc print data=PERSONNEL;
run;


The answer is suppose to be:

Obs ID DEPT BIRTHDAY YEAR SALARY
1 A123 A 10655 1989 8600
2 A037 A -922 1957 21450
3 M015 M 6106 1976 17500


I tried other settings but failing miserably. Please help. Needing advice.

 

[MBJ]

I am a little confused about your input of raw data. In 2) you have two values with . and an "a" last in the salary. There are many ways to reach your goal but if the input is as below you could use:

data PERSONNEL;
input ID $4. @1 DEPT $1. @6 BIRTHDAY DATE9. @6 YEAR Date9. SALARY comma7.;
Format Year Year4.;
CARDS;
A123 4Mar1989 8,600
A037 23Jun1957 21,450
M015 19Sep1976 17,500
run;
proc print data=PERSONNEL;
run;

Best regards

Morten

 

[Takumi]

There is no mistake in the data given. The "." and the "a" along with "*********" are all part of the data. But from using that data I have to input a program that gives me the final outcome as mentioned above. That is why I am so stuck right now. Is there any way to program so that SAS will ignore the *******, ".", and "a" in order to give me the final conclusion? I am still struggling.

 

[Janus]

Is this data stored in a text file?

 

[Takumi]

Original version is from SAS. There is no mistake with the spacing, the ".", "********", or the "a". I was told to write a program so that SAS would ignore all these unwanted data and to produce the result as mentioned above. I am so stuck. Please help

 

[MBJ]

If it was a real life exampke the first thing I would do was to call the guy who gave me the data and get him to do a better job in exporting it ) Secondly, I would just replace the unwanted characters with find and replace.

If you need a program that does it you could start importing the data as one long character string. Next, replace those unwanted characters with a string function and then do your import to a dataset with the wanted variables.

Morten