BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
molla
Fluorite | Level 6

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

3 REPLIES 3
Astounding
PROC Star

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
Super User Tom
Super User

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
Obsidian | Level 7
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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