Solved
New Contributor
Posts: 2

# seperating number and date

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

Accepted Solutions
Solution
a month ago
PROC Star
Posts: 1,592

## Re: seperating number and date

``````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;``````

All Replies
Solution
a month ago
PROC Star
Posts: 1,592

## Re: seperating number and date

``````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;``````
Posts: 1,312

## Re: seperating number and date

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.

New Contributor
Posts: 2

thanks

Super User
Posts: 23,320

## Re: seperating number and date

[ Edited ]

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

Posts: 1,312

## Re: seperating number and date

@Reeza:

I think you overlooked the day-component of the date.
Super User
Posts: 23,320

## Re: seperating number and date

@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

Super User
Posts: 10,691

## Re: seperating number and date

``````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;``````
☑ This topic is solved.