Converting CSV character dates to SAS dates

Reply
Occasional Contributor
Posts: 16

Converting CSV character dates to SAS dates

I have the following question to answer:

 

The main outcome of interest is whether the defendant was arrested again prior to his/her disposition (trial or court appearance) date. create a variable called 're_arrest' that equals one if the defendant was arrested prior to trial and zero otherwise e.g. imagine person 1 was arrested three times

 

caseid personid arrest_date    dispos_date  re_arrest

123        1          2012-01-01     2012-04-05    0

1344       1         2012-06-05     2012-09-13     1

1451       1         2012-07-09     2012-11-21     0

 

The dates a character value so i want to convert them into a sas date. I was thinking of approaching this by creating an if and then statement so if the date is in between the disposition date it would create 1 in the new variable. However, I'm having trouble inputting the csv file into sas and converting it into a sas date. Can anyone help me and if my approach is feasible? 

SAS Employee
Posts: 24

Re: Converting CSV character dates to SAS dates

Can I ask how you're reading in the CSV file?

Occasional Contributor
Posts: 16

Re: Converting CSV character dates to SAS dates

Posted in reply to antonbcristina
I'm tired using filename infile procedure and proc import but none to my
luck
SAS Employee
Posts: 24

Re: Converting CSV character dates to SAS dates

Can you post your code and log? It will help to figure out what is going on.

Occasional Contributor
Posts: 16

Re: Converting CSV character dates to SAS dates

Posted in reply to antonbcristina

filename inp1 'c:desktop\case.csv';

 

data case;

infile inp1;

informat arrest_date mmddyy10. dispos_date mmddyy10.;

input case_id arrest_date dispos_date;

run;

 

proc print;

run;

Highlighted
Super User
Posts: 13,536

Re: Converting CSV character dates to SAS dates


ogarduno wrote:

filename inp1 'c:desktop\case.csv';

 

data case;

infile inp1;

informat arrest_date YYMMDD10. dispos_date YYMMDD10.;

input case_id arrest_date dispos_date;

run;

 

proc print;

run;


Your dates obviously start with Years not months. You should have gotten many "invalid data" messages in the log.

It usually helps to add a FORMAT statement to display the values when you read them as well.

Super User
Posts: 13,536

Re: Converting CSV character dates to SAS dates


ogarduno wrote:

I have the following question to answer:

 

The main outcome of interest is whether the defendant was arrested again prior to his/her disposition (trial or court appearance) date. create a variable called 're_arrest' that equals one if the defendant was arrested prior to trial and zero otherwise e.g. imagine person 1 was arrested three times

 

caseid personid arrest_date    dispos_date  re_arrest

123        1          2012-01-01     2012-04-05    0

1344       1         2012-06-05     2012-09-13     1

1451       1         2012-07-09     2012-11-21     0

 

The dates a character value so i want to convert them into a sas date. I was thinking of approaching this by creating an if and then statement so if the date is in between the disposition date it would create 1 in the new variable. However, I'm having trouble inputting the csv file into sas and converting it into a sas date. Can anyone help me and if my approach is feasible? 


Show 1) the code you used to create the SAS data set from the CSV and 2) a copy few lines of the data AS TEXT (do not use Excel use something like Notepad or even the SAS editor), paste a few rows of the data into a code box here opened using the forum {I}

icon. Change any sensitive data before pasting it here.

 

The dates should read just fine with proc import unless 1) they have quotes around them in the CSV such as

123,1,'2012-01-01','2012-04-05',0

or the dates come in different formats or possibly the first few rows are missing the dates. Proc Import guesses variable types and formats from a small number of rows and changes or missing values make those "guesses" less useful.

 

Ask a Question
Discussion stats
  • 6 replies
  • 159 views
  • 0 likes
  • 3 in conversation