topic Re: seperating number and date in SAS Data Management

seperating number and date

solmaz — Wed, 16 May 2018 17:18:27 GMT

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

Re: seperating number and date

novinosrin — Wed, 16 May 2018 17:31:33 GMT

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;

Re: seperating number and date

mkeintz — Wed, 16 May 2018 18:23:04 GMT

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.

Re: seperating number and date

Reeza — Wed, 16 May 2018 19:25:02 GMT

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

Re: seperating number and date

mkeintz — Wed, 16 May 2018 19:25:51 GMT

@Reeza:

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

Re: seperating number and date

Reeza — Wed, 16 May 2018 19:38:41 GMT

@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 😉

Re: seperating number and date

solmaz — Thu, 17 May 2018 01:22:44 GMT

thanks

Re: seperating number and date

Ksharp — Thu, 17 May 2018 13:17:07 GMT

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;