DATA Step, Macro, Functions and more

SAS Concatenation Operator within FIRST. and LAST. variables

Reply
Occasional Contributor
Posts: 9

SAS Concatenation Operator within FIRST. and LAST. variables

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 4th to 8th records: Both 4th and 5th have the TOTAL_AMOUNT = 10000 as for the AMOUNT because the EARN_ID changes not. 6th has TOTAL_AMOUNT = 20000 (10000 + 10000) and 7th 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??  

Super Contributor
Posts: 259

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

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 ; 

...

PROC Star
Posts: 7,363

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

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.


Ask a Question
Discussion stats
  • 2 replies
  • 138 views
  • 0 likes
  • 3 in conversation