Creating Another Column

[Eugenio211]

Hi - hope everyone is fine.

attached is the sample data

what I wanted to do is to create a new column (OtherLoc)  from referencing location column.

In logic, what I wanted to get is that -  if 'Location' from Obs1 is not equal Obs2 then OtherLoc = 1, for each Customer.

Thank you in advance.

[ballardw]

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Your logic also needs some clarification. Since you explicitly state Obs1 vs Obs2, what about Obs3, 4, 5 etc?.

Do want to compare each observation with the previous observation or each observation only with the first location observed for the customer?

Since you apparently may have a date value, has the data been sorted to ensure that the first date for each customer is the earliest?

If sorted properly and you want to compare all observations to the First location then something like:

data want;
   set have;
   by customer;
   retain compareloc ;
   if first.customer then compareloc=location;
   else OtherLoc = (comparloc ne Location);
   drop compareloc;
run;

By will expect the data to be sorted by the customer variable. If the values are not alphabetical then you likely need to sort the source data set by customer and date. Note that date variable is not a SAS date value it won't sort properly.

If the location is a character then you need something like LENGTH COMPARELOC $ 10; before the Retain to make sure the variable can hold the longest expected value. Retain will keep the value of a variable across the record boundary.

The FIRST. (note the dot is very important) is a temporary variable that indicates this is the first of a by variable value group when using a BY statement. There is also a LAST. for each variable. These are values of 1 for true and 0 for false so can be used in IF as shown. The temporary variables are note kept in the resulting data.

Drop removes the comparison value.