How to copy the records/observations from previous row under certain conditions?

Reply
Super User
Posts: 8,359

Re: How to copy the records/observations from previous row under certain conditions?

Then post your mock dataset in a data step for easy recreation.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 8,359

Re: How to copy the records/observations from previous row under certain conditions?

The usual approach for this kind of task is to use retained variables that are set at first., but I wanted to reduce the amount of code.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Highlighted
Super User
Posts: 5,728

Re: How to copy the records/observations from previous row under certain conditions?

Can we assume that every CASE_NO has exactly one observation with a blank TFC, and at least one observation with a nonblank TFC?

 

In that case, here is an approach:

 

data want;

set have (where=(TFC=' ')) ;

output;

do until (last.case_no) ;

   set have (keep=case_no TFC DocType_A where=(TFC > ' ')) ;

   output;

end;

run;

Occasional Contributor
Posts: 11

Re: How to copy the records/observations from previous row under certain conditions?

Posted in reply to Astounding

Hi Astounding,

 

Thanks for the reply and your suggestion. But i actually have in total of 52 variables for this table, and i only wish to copy the first 6 variable's data, so is there any other approach where i don't have to use the "keep" statement in line 5 to key in all the other 40+ variables that i want to keep?

Super User
Posts: 8,359

Re: How to copy the records/observations from previous row under certain conditions?


Ant_K wrote:

Hi Astounding,

 

Thanks for the reply and your suggestion. But i actually have in total of 52 variables for this table, and i only wish to copy the first 6 variable's data, so is there any other approach where i don't have to use the "keep" statement in line 5 to key in all the other 40+ variables that i want to keep?


Do it the other way around and use a drop dataset option.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,728

Re: How to copy the records/observations from previous row under certain conditions?

Sure.  Replace keep= with drop= and add the names of the 6 variables that you don't want to replace.

Super User
Super User
Posts: 7,262

Re: How to copy the records/observations from previous row under certain conditions?

[ Edited ]

You can probably use the UPDATE statement to do what you want, but you will need to first blank out the values that you don't want anymore.  Let's convert your second example into a dataset so we have something to program with.

data have ;
  length No 8 Acc_No $8 Agent $20 Src $1 
         Branch_F $2 Exec $2 Trx $2 DocType_A 8 TFC $1 
         Case_No $8 
  ;
  infile cards dsd dlm='|' truncover ;
  input no -- case_no ;
cards;
1|KU1234|Fairview Enterprise|A|KU|KU|KU|2| |ABC6345
2|KL4321|AMG Marine|X|KL|KL|KL|3|C|ABC6345
3|KL4321|AMG Marine|X|KL|KL|KL|4|C|ABC6345
4|KL4322|Macro Trust Fund |X|KL|KL|KL|5|C|ABC6345
5|SB9564|Essentials Dotcom|B|SB|SB|SB|1| |CBA2134
6|KL3214|Visual Optics|X|KL|KL|KL|7| |CBA2134
7|KL3214|Visual Optics|X|KL|KL|KL|8| |CBA2134
;

Now let's make a copy where some of the columns are set to missing when the value of TFC='C'.  I will use CALL MISSING() to do this and use a position based variable list.

data for_update ;
  set have ;
  if tfc='C' then call missing(of Acc_no -- Trx);
run;

Now we can use this new dataset as the transaction dataset in an UPDATE statement. For the master dataset we will use an empty dataset, which we can get by using the dataset option OBS=0.  Since you have multiple observations per CASE_NO we will need to add an OUTPUT statement to write an observation after each "transaction" is applied.

data want ;
  update have(obs=0) for_update ;
  by case_no;
  output;
run;

Here are the results.

image.png

And here is variable summary from PROC COMPARE.

image.png

Occasional Contributor
Posts: 11

Re: How to copy the records/observations from previous row under certain conditions?

Dear Tom, Thanks for your solution, you are my saviour, i managed to solve my problem with your solution. Thanks again.
Ask a Question
Discussion stats
  • 22 replies
  • 238 views
  • 2 likes
  • 4 in conversation