topic Re: Converting CSV character dates to SAS dates in SAS Programming

Converting CSV character dates to SAS dates

ogarduno — Fri, 09 Feb 2018 17:59:00 GMT

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?

Re: Converting CSV character dates to SAS dates

antonbcristina — Fri, 09 Feb 2018 18:08:24 GMT

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

Re: Converting CSV character dates to SAS dates

ogarduno — Fri, 09 Feb 2018 18:13:51 GMT

I'm tired using filename infile procedure and proc import but none to my
luck

Re: Converting CSV character dates to SAS dates

antonbcristina — Fri, 09 Feb 2018 18:18:40 GMT

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

Re: Converting CSV character dates to SAS dates

ballardw — Fri, 09 Feb 2018 18:26:24 GMT

@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.

Re: Converting CSV character dates to SAS dates

ogarduno — Fri, 09 Feb 2018 18:26:38 GMT

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;

Re: Converting CSV character dates to SAS dates

ballardw — Fri, 09 Feb 2018 18:35:48 GMT

@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.