topic Re: Formula Help in SAS Programming

Formula Help

sasuser_sk — Tue, 20 Apr 2021 14:29:44 GMT

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 " "

Re: Formula Help

PaigeMiller — Tue, 20 Apr 2021 14:34:51 GMT

"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.

Re: Formula Help

sasuser_sk — Tue, 20 Apr 2021 14:40:19 GMT

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.

Re: Formula Help

ballardw — Tue, 20 Apr 2021 14:44:39 GMT

@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.

Re: Formula Help

sasuser_sk — Tue, 20 Apr 2021 15:00:51 GMT

Re: Formula Help

maguiremq — Tue, 20 Apr 2021 16:10:39 GMT

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

Re: Formula Help

Reeza — Tue, 20 Apr 2021 16:28:36 GMT

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:

Re: Formula Help

sasuser_sk — Tue, 20 Apr 2021 16:47:43 GMT

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.

Re: Formula Help

Reeza — Tue, 20 Apr 2021 17:32:35 GMT

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.