BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
solmaz
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
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

7 REPLIES 7
novinosrin
Tourmaline | Level 20
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
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
solmaz
Calcite | Level 5

thanks

Reeza
Super User

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
PROC Star
@Reeza:

I think you overlooked the day-component of the date.
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Reeza
Super User

@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
Super User
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

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