seperating number and date

[solmaz]

Hi 

I have a data base that two variables of salary and date is attached and I want to separate them as two column. I don't know  how to do it.  

$2,92720/09/1979

$115,58111/02/1974

$78,88425/07/1980

$13,64405/07/1977

[novinosrin · Accepted solution]

data have;
input var $20.;
datalines;
$2,92720/09/1979
$115,58111/02/1974
$78,88425/07/1980
$13,64405/07/1977
;

data want;
set have;
date=substr(var,index(var,'/')-2);
salary=substr(var,1,index(var,'/')-3);
run;

[mkeintz]

Are your dates always dd/mm/yyyy?  Is so, then you know that the last ten characters of the input line (variable _INFILE_ below) are dates, and the first X characters (where X is length of _INFILE_-10) is the salary:

data want;
  input;
  date=input(substr(_infile_,length(_infile_)-9),ddmmyy10.);
  salary=input(substr(_infile_,1,length(_infile_)-10),dollar8.0);
  format date date9.  salary dollar8.0;
datalines;
$2,92720/09/1979
$115,58111/02/1974
$78,88425/07/1980
$13,64405/07/1977
run;

Whenever there is an INPUT statement, the automatic variable _INFILE_ is generated, containing the contents of the input data line.  But, as an automatic variable _INFILE_ is not output to the resulting data set.  But that's what you want, since the objective is to extract two new variables from the _INFILE_ contents.

The SUBSTR functions extracts a substring of a character variable.  So

   substr(name,3,6) gets characters 3 through 6.

   substr(name,7)  gets all characters starting with character 7.

[solmaz]

thanks

[Reeza]

Is your data in a text file or already in a SAS data set?

If it's in a text file, your best bet is to read it correctly in the first place:

data have;
infile cards dlm='/';
informat amount dollar12. month  year 8.;
input amount month year;
datalines;
$2,92720/09/1979
$115,58111/02/1974
$78,88425/07/1980
$13,64405/07/1977
;

run;

---

@solmaz wrote:

Hi 

I have a data base that two variables of salary and date is attached and I want to separate them as two column. I don't know  how to do it.  

$2,92720/09/1979

$115,58111/02/1974

$78,88425/07/1980

$13,64405/07/1977

---

[mkeintz]

@Reeza:

I think you overlooked the day-component of the date.

[Reeza]

@mkeintz That assumes there is a day component. It could easily be a number and the month and year only. It could go either way and the OP did not specify that it was a full date, versus just month/year. You're probably correct though 😉

[Ksharp]

data have;
input var $20.;
dollar=prxchange('s/\d\d\/\d\d\/\d\d\d\d$//',1,strip(var));
ddmmyy=prxchange('s/.*(\d\d\/\d\d\/\d\d\d\d)$/$1/',1,strip(var));
datalines;
$2,92720/09/1979
$115,58111/02/1974
$78,88425/07/1980
$13,64405/07/1977
;

proc print noobs;run;