Date formats

[molla]

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:

 

[Astounding]

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.

[Tom · Accepted solution]

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.

[Bipasha]

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