topic Re: How to handle special characters in data in SAS Programming

How to handle special characters in data

torvin10 — Wed, 14 Nov 2018 07:03:14 GMT

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?

Re: How to handle special characters in data

MichaelLarsen — Wed, 14 Nov 2018 07:19:27 GMT

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 ***************

Re: How to handle special characters in data

Kurt_Bremser — Wed, 14 Nov 2018 07:26:04 GMT

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;