SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

seperating number and date

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

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;

View solution in original post


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;
Trusted Advisor
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

Re: seperating number and date

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



 

Trusted Advisor
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 Smiley Wink

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 253 views
  • 3 likes
  • 5 in conversation