Check all rows by specific KEY and create a new variable

[Sk1_SAS]

Hi Team,

I need to create a field containing the names of the variables that have changed for each specific key, ex:

DATA YourTable;
    INPUT Key $ Date :date9. Date2 :date9. Customer :$10.;
    FORMAT Date Date2 date9.;
    DATALINES;
M335 17-Sep-21 20-Sep-21 2913401266
M335 17-Sep-21 20-Sep-21 2913401266
M335 2-Dec-21 20-Sep-21 3885540104
M335 2-Dec-21 20-Sep-21 3885540104
M335 2-Dec-21 20-Sep-21 3885540104
M335 2-Dec-21 20-Sep-21 3885540104
;

i need to create a new variable containing which field has changed, in this case: Date and Customer,

i tried, but it did not work, suggestion? 

DATA Changes;
    SET YourTable;
    BY Key;

    IF FIRST.Key THEN DO;
        New_Variable = ""; 
        Prev_Date = Date;
        Prev_Date2 = Date2;
        Prev_Customer = Customer;
    END;

    IF NOT FIRST.Key THEN DO;
        IF Date NE Prev_Date THEN New_Variable = CATX(",", New_Variable, "Date");
        IF Date2 NE Prev_Date2 THEN New_Variable = CATX(",", New_Variable, "Date2");
        IF Customer NE Prev_Customer THEN New_Variable = CATX(",", New_Variable, "Customer");
    END;

    IF LAST.Key THEN OUTPUT; 

    DROP Prev_:;

    FORMAT New_Variable $200.; 
RUN;

;

[PaigeMiller]

i tried, but it did not work, suggestion? 

What about it did not work? Please tell us. If there was an ERROR in the log, show us the ENTIRE log for this code. If the results were not correct, show us the results and explain what you expected to see.

It is never good to say something didn't work and then provide no additional explanation.

[Sk1_SAS]

sorry, the code i provided worked but the result is:

the new variable is only "D", i might did something wrong in the code.

[PaigeMiller · Accepted solution]

When something isn't correct, you need to tell us what the correct answer is. I specifically asked for that, but all you show us is the wrong answer. We still don't know what the expected answer is.

So, I am going to make a guess here. It seems that you just need the following statement in your DATA CHANGES.

Otherwise the length of the variable new_variable is set the first time something is assigned to new_variable, and that's too short

length new_variable $ 24;

You also do not need the FORMAT statement.

[Tom]

Since you did not define NEW_VARIABLE the data step compiler GUESSED that you wanted it to be defined with a length of 1 since you first referenced in a line where you assigned it a string literal of one space character.

[ballardw]

I am going to guess that the intent was to keep the value of New_variable and those other variables across the data step for each row. That would be the RETAIN statement. Below just does the New_variable.

DATA Changes;
    SET YourTable;
    BY Key;
    length New_variable $ 200;
    retain New_variable;

    IF FIRST.Key THEN DO;
        New_Variable = ""; 
        Prev_Date = Date;
        Prev_Date2 = Date2;
        Prev_Customer = Customer;
    END;

    IF NOT FIRST.Key THEN DO;
        IF Date NE Prev_Date THEN New_Variable = CATX(",", New_Variable, "Date");
        IF Date2 NE Prev_Date2 THEN New_Variable = CATX(",", New_Variable, "Date2");
        IF Customer NE Prev_Customer THEN New_Variable = CATX(",", New_Variable, "Customer");
    END;

    IF LAST.Key THEN OUTPUT; 

    DROP Prev_:;

    FORMAT New_Variable $200.; 
RUN;

You may need to make the Length (and format) of New_variable much longer.

or show what you expect for the result.