SAS Concatenation Operator within FIRST. and LAST. variables

willow2010 — Mon, 22 Aug 2011 05:27:04 GMT

I have a piece of SAS code:

DATA EANEADTGP(KEEP= ID DATE_LAST_EARNING AMOUNT_LAST_EARNING

PHONE_LAST_EMPLOYER EARN_ID

TOTAL_AMOUNT_LAST_EARNING

FULL_LIST_PHONE_LAST_EMPLOYER)

;

LENGTH TOTAL_AMOUNT_LAST_EARNING 8

FULL_LIST_PHONE_LAST_EMPLOYER $130

;

SET EANEADTBUILDUP ;

BY ID DATE_LAST_EARNING ;

IF FIRST.DATE_LAST_EARNING THEN DO ;

TOTAL_AMOUNT_LAST_EARNING = 0 ;

FULL_LIST_PHONE_LAST_EMPLOYER = '*' ;

END ;

LAGEARN_ID=LAG(EARN_ID) ;

IF LAGEARN_ID NE EARN_ID THEN DO ;

TOTAL_AMOUNT_LAST_EARNING + AMOUNT_LAST_EARNING ;

FULL_LIST_PHONE_LAST_EMPLOYER=TRIM(FULL_LIST_PHONE_LAST_EMPLOYER)

||'/'||TRIM(PHONE_LAST_EMPLOYER) ;

END;

ELSE DO;

TOTAL_AMOUNT_LAST_EARNING = AMOUNT_LAST_EARNING ;

FULL_LIST_PHONE_LAST_EMPLOYER = PHONE_LAST_EMPLOYER ;

END;

RETURN ;

And here is some of my output (some field contents are being modified and PHONE_LAST_EMPLOYER is a $15 field):

FULL_LIST_ DATE_

TOTAL_AMOUNT_ PHONE_LAST_ LAST_ AMOUNT_LAST_ PHONE_LAST_

LAST_EARNING EMPLOYER EARNING EARNING EMPLOYER ID EARN_ID

9000 */12345678 20091125 9000 12345678 111111111 13

32300 /_ 20091125 23300 _ 111111111 18

40300 /_ 20091125 8000 _ 111111111 19

10000 */_ 20070926 10000 _ 222222222 3

10000 _ 20070926 10000 _ 222222222 3

20000 /_ 20070926 10000 _ 222222222 4

22800 /_ 20070926 2800 _ 222222222 5

13500 */0788884444 20100326 13500 0788884444 333333333 10

73500 /_ 20100326 60000 _ 333333333 11

96000 /_ 20100326 22500 _ 333333333 12

124050 /0766446644 20100326 28050 0766446644 333333333 9

Looks like the TOTAL_AMOUNT_LAST_EARNING works. E.g., looks at the 4^th to 8^th records: Both 4^th and 5^th have the TOTAL_AMOUNT = 10000 as for the AMOUNT because the EARN_ID changes not. 6^th has TOTAL_AMOUNT = 20000 (10000 + 10000) and 7^th has TOTAL_AMOUNT=22800(10000+10000+2800).

However FULL_LIST_PHONE_LAST_EMPLOYER does not seem to have what I expected in return. E.g., the last record I expect to be: */0788884444/_/_/0766446644 and it’s not happening. (At this stage I’m really not fuzzy in getting rid of ‘_’) And yet looks like FULL_LIST can only get the PHONE_LAST_EMPLOYER from the current record and forget everything else in the past or being reset as ‘ ‘. How did it happen? Do I need to make FULL_LIST_PHONE_LAST_EMPLOYER into some sort of array or nested loop??

Re: SAS Concatenation Operator within FIRST. and LAST. variables

andreas_lds — Mon, 22 Aug 2011 07:19:31 GMT

The variable FULL_LIST_PHONE_LAST_EMPLOYER is reset to missing in each iteration, to preserve the value you need to add a retain statement:

length ....;

retain FULL_LIST_PHONE_LAST_EMPLOYER;

SET EANEADTBUILDUP ;

...

Re: SAS Concatenation Operator within FIRST. and LAST. variables

art297 — Mon, 22 Aug 2011 12:06:58 GMT

While Andreas already answered your question, I thought you might find it equally useful to know that the reason that you don't have to include Total_Amount_Last_Learning in a retain statement is because it is automatically retained because you used in the statement TOTAL_AMOUNT_LAST_EARNING + AMOUNT_LAST_EARNING ;

The variable on the left in such sum statements are automatically retained.

topic Re: SAS Concatenation Operator within FIRST. and LAST. variables in SAS Programming

SAS Concatenation Operator within FIRST. and LAST. variables

Re: SAS Concatenation Operator within FIRST. and LAST. variables

Re: SAS Concatenation Operator within FIRST. and LAST. variables