topic Re: Copying cell values in SAS Data Management

Copying cell values

Raj21 — Mon, 25 Jul 2016 16:40:05 GMT

I am trying to copy one cell to the next cell when another column has the same value. I have been using this code, but it doesn't seem to be working the way i want.

if TRIP_ID=Lag(TRIP_ID) then CHRG_CD=lag(CHRG_CD);

The trip_ID is 349333

10791391	349333
10791390	349333	3892B
10791390	349333	3892B

Re: Copying cell values

Astounding — Mon, 25 Jul 2016 17:27:43 GMT

That's a common mistake. The LAG function does not retrieve the value from the previous observation. It actually does something a little bit different. It retrieves the value from the last time that the LAG function executed.

Fixing this will be relatively simple. But you need to provide a little more information. In the data you provided, what are the column names? What end result would you like to see?

Re: Copying cell values

Raj21 — Mon, 25 Jul 2016 17:30:20 GMT

The first column is Load Leg, the second column is Trip ID and the third column is Bundle. For this observation I would like it to use the Trip id which would be the same and copy the bundle value. There are many more observations, so I would want that logic for the whole table.

Re: Copying cell values

Astounding — Mon, 25 Jul 2016 17:36:05 GMT

So where does CHRG_CD come into the program?

Are you trying to fill in the blank cell with the later value from Bundle?

Re: Copying cell values

Raj21 — Mon, 25 Jul 2016 17:55:20 GMT

Sorry CHRG_CD is bundle and yes that is what I would be trying to do .

Re: Copying cell values

Astounding — Mon, 25 Jul 2016 18:02:34 GMT

OK. Most solutions will assume that the data is in order such as BY TRIP_ID. If that's not possible, more difficult approaches are possible.

There also may be different BUNDLE values within the same TRIP_ID that will get replaced with a common value.

At any rate here's one possibility that assumes a sorted order:

data want;

do until (last.TRIP_ID);

set have;

by trip_ID;

if bundle > ' ' then replacement_bundle=bundle;

end;

do until (last.TRIP_ID);

set have;

by trip_ID;

output;

end;

drop bundle;

rename replacement_bundle=bundle;

run;

The top loop finds a nonmissing BUNDLE for that TRIP_ID. The bottonm loop reads the same observations and uses that nonmissing value as a replacement value.

Re: Copying cell values

Raj21 — Mon, 25 Jul 2016 18:28:52 GMT

I can organize this by Trip_ID, that shouldn't be a problem. That do loop worked like a charm, thank you very much!