topic Date formats in SAS Programming

Date formats

molla — Sun, 23 Jul 2017 13:56:53 GMT

Hi,

I have the below data :

data test2;
input CustomerId $1-6 ScoreCardId 7-11 CustomerCreditGrade $13-16
ScoreCardDate $18-27 ScoreCardApprovedFlag $;
informat ScoreCardDate ddmmyy10.;
format ScoreCardDate yymmdd10.;
cards;
X1111 32104 AA+ 31/12/2016 Y
X1111 32105 AAA 31/03/2017 N
X1111 32103 AA 30/6/2106 Y
X1111 32102 AA- 31/03/2016 Y
X2222 40321 BBB 31/12/2016 N
X2222 40322 BBB+ 31/12/2016 Y
;
run;

I want the below

output:

X1111 32105 AAA 31/03/2017 N
X2222 40321 BBB 31/12/2016 N
X2222 40322 BBB+ 31/12/2016 Y

the code which I tried:

proc sql;
create table test3 as
select CustomerId,ScoreCardId,CustomerCreditGrade,ScoreCardDate,
ScoreCardApprovedFlag
from test2 group by CustomerId
having ScoreCardDate=max(ScoreCardDate) ;
quit;

The output which am getting is attached below:

Re: Date formats

Astounding — Sun, 23 Jul 2017 10:09:12 GMT

Your difficulties begin with the fact that you made the date a character variable. Get rid of the dollar sign here:

ScoreCardDate $18-27

That might be enough to fix everything.

Re: Date formats

Tom — Sun, 23 Jul 2017 14:01:06 GMT

You data does not appear to be in fixed columns like you INPUT statement is assuming. Perhaps it really is, but the formatting was lost when you pasted the code into the text box instead of using the Insert SAS code pop-up text window.

I have editted you message to use SAS code boxes.

Do not read your dates as strings. You should have gotten an note that SAS couldn't find the $DDMMYY informat.

Re: Date formats

Bipasha — Sun, 23 Jul 2017 17:44:42 GMT

Making ScoreCardDate a numeric variable should fix the issue.
Here since the varible is a charecter variable when you use max it takes the value of variable with max frequency as it cannot determine the max of string values