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
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;
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;
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.
thanks
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 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 😉
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.