DATA Step, Macro, Functions and more

Date formats

Accepted Solution Solved
Reply
Contributor
Posts: 65
Accepted Solution

Date formats

[ Edited ]

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:

 

 Capture (4).PNG

 

 


Accepted Solutions
Solution
‎07-23-2017 02:48 PM
Super User
Super User
Posts: 7,076

Re: Date formats

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.

View solution in original post


All Replies
Super User
Posts: 5,516

Re: Date formats

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.

Solution
‎07-23-2017 02:48 PM
Super User
Super User
Posts: 7,076

Re: Date formats

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.

Occasional Contributor
Posts: 15

Re: Date formats

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
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 226 views
  • 0 likes
  • 4 in conversation