Formula Help

[sasuser_sk]

Hello Everyone - I'm looking for some help in writing the formula given below in sas. Please advise. Thank you!

if(Duplicate !=null && Product != Next(Product)) then "Delete"

if(Duplicate !=null && Product = 'CLOSED' && Next(Product) = 'CLOSED') then "Delete"

else " "

[PaigeMiller]

"Delete" as you are using it should not be in quotes, unless I am mis-understanding what you are doing. What are you trying to do with "Delete"??

When you use NULL, do you really mean checking for SAS missing values?

Why don't you have semi-colons at the end of each line?

Why the double-ampersands? I know MATLAB uses double-ampersands to have specific meaning, SAS does not.

[sasuser_sk]

This code/formula is not form SAS but from Hyperion. I'm trying to figure out the same formula in SAS. I edited it a bit to show what I intend to achieve in SAS.

I'm creating a new column with this formula. Delete is just an indicator in this new column that I will filter out later. It could be 0 or remove instead of Delete.

By !null I means if Duplicate is not missing.

[ballardw]

---

@sasuser_sk wrote:

Hello Everyone - I'm looking for some help in writing the formula given below in sas. Please advise. Thank you!

if(Duplicate !=null && Product != Next(Product)) then "Delete"

if(Duplicate !=null && Product = 'CLOSED' && Next(Product) = 'CLOSED') then "Delete"

else " "

---

So, what does that "formula" do? Provide example data of start and end result.

[sasuser_sk]

[maguiremq]

I think this gives you what you want. I would recommend making a snippet of data available for us in the form of a 'have' data set and a 'want' data set. It makes it a lot easier than eyeballing an excel screenshot.

I don't know if this is how your data is structured either. Things may change depending on what you have as well as additional records.

data have;
input id duplicate product :$6.;
datalines;
7448 0 CLOSED
7448 . ACTIVE
71802 . ACTIVE
73627 0 CLOSED
73627 . ACTIVE
72092 0 CLOSED
72092 . ACTIVE
701992 . ACTIVE
7282 0 CLOSED
7282 0 CLOSED
7282 . ACTIVE
7363 0 CLOSED
7363 . ACTIVE
739 0 CLOSED
739 . CLOSED
;
run;

proc sort data = have;
	by id duplicate;
run;


data want;
	set have;
	by id;
	lag_product = lag(product);
		if first.id then call missing(lag_product);
		if not missing(duplicate) and product ^= lag_product then want_column = "delete";
			else if not missing(duplicate) and (product = "CLOSED" and lag_product = "CLOSED") then want_column = "delete";
				else call missing(want_column);
run;

Obs id duplicate product lag_product want_column 
1 739 . CLOSED     
2 739 0 CLOSED CLOSED delete 
3 7282 . ACTIVE     
4 7282 0 CLOSED ACTIVE delete 
5 7282 0 CLOSED CLOSED delete 
6 7363 . ACTIVE     
7 7363 0 CLOSED ACTIVE delete 
8 7448 . ACTIVE     
9 7448 0 CLOSED ACTIVE delete 
10 71802 . ACTIVE     
11 72092 . ACTIVE     
12 72092 0 CLOSED ACTIVE delete 
13 73627 . ACTIVE     
14 73627 0 CLOSED ACTIVE delete 
15 701992 . ACTIVE     

[Reeza]

It's possible your logic is more complicated but right now that could be simplified to delete all CLOSED + DUPLICATE records. 

Can you please show a case where you have a CLOSED record that is not deleted?

if duplicate = 0 and product = "CLOSED" then new_column = "DELETE";

---

@sasuser_sk wrote:

---

[sasuser_sk]

Reeza, after creating the new_column I have to make further comparisons to several other fields (ex: dates) based on this new column and build logic on the gaps between the closed products. That would be my next step after I create this new column. 

I'm trying to break steps into smaller formulas to avoid creating complex ones.

Building looped formula makes it difficult (for me) to figure out what is working or not.

[Reeza]

I think you're using Excel logic in SAS and it's taking you a long way around doing something that would be likely easier in SAS using native functionality.

And please don't post anymore screenshots, post your data as text which makes it easier to use your data to help answer your questions.