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

Copying cell values

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

Copying cell values

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

 

10791391349333 
107913903493333892B
107913903493333892B

 

 


Accepted Solutions
Solution
‎07-25-2016 01:54 PM
Super User
Posts: 5,092

Re: Copying cell values

So where does CHRG_CD come into the program?

 

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

View solution in original post


All Replies
Super User
Posts: 5,092

Re: Copying cell values

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?

 

 

Occasional Contributor
Posts: 16

Re: Copying cell values

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.


Solution
‎07-25-2016 01:54 PM
Super User
Posts: 5,092

Re: Copying cell values

So where does CHRG_CD come into the program?

 

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

Occasional Contributor
Posts: 16

Re: Copying cell values

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


Super User
Posts: 5,092

Re: Copying cell values

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.

Occasional Contributor
Posts: 16

Re: Copying cell values

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


☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 350 views
  • 0 likes
  • 2 in conversation