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:
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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.