SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Creating a flag for the first time a value appears

Reply
Occasional Contributor
Posts: 6

Creating a flag for the first time a value appears

Hi,

I am new to DI and I am not sure if this has been answered elsewhere, but does anyone know how to create a flag or a counter for the first time a value appears for a dataset?

In SAS BASE code, I use:

data output;

set input;

by variable;

if first.variable then count = 1;

run;

Any help on this would be extremely helpful. If anything doesn't make sense let me know.

Thanks in advance.

Trusted Advisor
Posts: 1,137

Re: Creating a flag for the first time a value appears

I believe this is the right approach.

first sort the dataset on the variables in which you want the data to sort and then  use first.var to flag the record. Please let me know if this has not worked for you or provide a sample data .

Thanks,

jag

Thanks,
Jag
Occasional Contributor
Posts: 6

Re: Creating a flag for the first time a value appears

Posted in reply to Jagadishkatam

Hi Jag,

Thanks for the quick response. The approach I mentioned works in EG but I need a DI method that does the same thing.

I have searched online and tried to use ROWNUMBER or ROWCOUNT but DI informs me that it cannot find those functions.

Cheers

Kimberley

Super User
Posts: 19,815

Re: Creating a flag for the first time a value appears

Is monotonic available?

Occasional Contributor
Posts: 6

Re: Creating a flag for the first time a value appears

I was able to use monotonic!

I ended up using monotonic to create a row number and then using min() to get the lowest row number for my variable. Followed by applying a boolean value where 1 was the min row number and 0 was any succeeding row.

Thank you for your help!!

Super User
Posts: 19,815

Re: Creating a flag for the first time a value appears

Just a warning that it's considered unsupported and shouldn't be used in production - so use at your own risk Smiley Sad

Occasional Contributor
Posts: 6

Re: Creating a flag for the first time a value appears

Thanks for the warning, I'll use it for testing and validation purposes but keep it out of my production jobs.

In the meantime, I'll keep hunting for a supported method :smileygrin:.

Super User
Posts: 5,429

Re: Creating a flag for the first time a value appears

You can't use monotonic() interchangeable with first. processing of the data step. It' more like the _n_ variable.

But if it fits your current need, great.

To be able to use first. processing in DIS, you need to use User Written code, there is no "pure" data step transformation available.

About montonic() being unsupported, one funny thing is that is now being used within the Data Validation transformation. Wonder if SAS is supporting that...

Data never sleeps
Ask a Question
Discussion stats
  • 7 replies
  • 1109 views
  • 5 likes
  • 4 in conversation