topic Re: SAS Data Integration: Lag Function in SAS Data Management

SAS Data Integration: Lag Function

Squack — Thu, 01 Nov 2018 13:34:38 GMT

Good morning, I am writing to illustrate a problem that I just can not solve.

this is the data set in question (see attachment).

As you can see, the Data variable has missing values, and I should fill in those values by taking what is above.
For example, if the date corresponds to June 30 in the first line, then also the next two lines must contain June 30th.

I am aware that using the LAG1 and LAG2 function I could solve the problem (as I did in SAS base 9.4). I would like to be able to reproduce this in SAS Data Integration, but I have many difficulties.
Could I kindly have any suggestions?

Thank you

Re: SAS Data Integration: Lag Function

RW9 — Thu, 01 Nov 2018 13:58:22 GMT

Lag will not work there, nor will it work that way in base SAS. What you would do in Base is retain a value, then populate the other on that if its missing, e.g

data want;
  set have;
  retain lst_date;
  if date ne . then lst_date=date;
  else date=lst_date;
run;

Re: SAS Data Integration: Lag Function

Squack — Thu, 01 Nov 2018 14:03:35 GMT

First of all, I thank you for the help you are giving me! 🙂

Ok, I'll try this code in SAS base but what you say makes sense.

Can you help me in Data Integration? More than anything else I would like to understand what nodes to use to reproduce this code...

Re: SAS Data Integration: Lag Function

RW9 — Thu, 01 Nov 2018 14:14:45 GMT

I am afraid I know nothing about DIS, perhaps someone else from the community will be able to assist.

Re: SAS Data Integration: Lag Function

Squack — Thu, 01 Nov 2018 14:16:32 GMT

Thanks anyway for answering for SAS 9.4 🙂

Re: SAS Data Integration: Lag Function

LinusH — Thu, 01 Nov 2018 15:20:16 GMT

For DI Studio, you need to create a User Written code transformation.

Or if you think you can write reusable code, a User Written Transformation.

If you need further help, specify what you need help with.

Re: SAS Data Integration: Lag Function

Squack — Thu, 01 Nov 2018 15:36:57 GMT

Thanks for the reply LinusH!
I had also thought about using the User Written code transformation...
It is very recently that I use DI and this particular node creates many problems ..
The main thing is that it almost never finds the dataset I'm using.
Maybe I'm wrong ...
Can you tell me the correct procedure to use it?

Re: SAS Data Integration: Lag Function

LinusH — Thu, 01 Nov 2018 16:06:50 GMT

Can you clarify what you mean by "it almost never finds the data set I'm using"?

Writing efficient and roubust DI code is craftsmanship, and there's papers and documentation available.

Short, you need to use the DI Studio macro variables for input and output data set, that is the baseline. In this way, any linked table will be automatically "found", given that the rest of the job and the environment as such is stable and maintained in the correct way.