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

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

Ant_K — Wed, 03 Jan 2018 07:48:33 GMT

Dear all,

First of all, happy new year everyone. I'm a new user in using SAS program, and currently my company is using SAS Enterprise Guide for company's management reports.

I'm currently stuck with this issue as below example, as in how to massage/amend the observations extracted from the system database:

Acc_No	Agent	Src	Branch_F	Exec	Trx	DocType_A	TFC	Case No
KU1234	Fairview Enterprise	A	KU	KU	KU	2		ABC6345
KL4321	AMG Marine	X	KL	KL	KL	3	C	ABC6345
KL4321	AMG Marine	X	KL	KL	KL	4	C	ABC6345
KL4322	Macro Trust Fund	X	KL	KL	KL	5	C	ABC6345

To this using SAS:

Acc_No	Agent	Src	Branch_F	Exec	Trx	DocType_A	TFC	Case No
KU1234	Fairview Enterprise	A	KU	KU	KU	2		ABC6345
KU1234	Fairview Enterprise	A	KU	KU	KU	3	C	ABC6345
KU1234	Fairview Enterprise	A	KU	KU	KU	4	C	ABC6345
KU1234	Fairview Enterprise	A	KU	KU	KU	5	C	ABC6345

The scenario is, when the data/records downloaded are sorted by column "Case No" & "TFC", those rows with TFC = "C" (in this example, which is row 2 to row 4) will copy the observations/information from Column "Acc_No" until "Trx" from the very first row of the same "Case No".

Hope the experts here can help this beginner here.

Thanks.

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

Kurt_Bremser — Wed, 03 Jan 2018 08:22:53 GMT

You say that only Acc_no should be manipulated, but in your example result all columns except doctype_a, case_no and tfc are changed.

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

Ant_K — Wed, 03 Jan 2018 08:27:50 GMT

Hi Kurt, i meant from the 1st column until the sixth column, i.e. "Acc_No","Agent", "Src", "Branch_F", "Exec" & "Trx" all six columns will copy from the 1st row, under the condition of same "Case No".

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

Kurt_Bremser — Wed, 03 Jan 2018 08:38:06 GMT

Oh, that's what you meant with "until". "up to" would have been more precise.

Try something like this:

data preserve;
set have (keep=acc_no agent src branch_f exec trx case_no);
by case_no;
if first.case_no;
run;

data want;
update
  have
  preserve
;
by case_no;
run;

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

Ant_K — Wed, 03 Jan 2018 08:45:16 GMT

Thanks Kurt, pardon my English and sorry for the confusion.

Thanks for your quick response, i will try it out.

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

Ant_K — Wed, 03 Jan 2018 10:14:24 GMT

Hi Kurt,

I'm getting warning message from SAS:

WARNING: The MASTER data set contains more than one observation for a BY group. ( -> i presumed "observation" here means row in my context right?)

And the programming code didn't work as the data/records in column 1 - 6 of row 2, 3 & 4 still remains the same.

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

Kurt_Bremser — Wed, 03 Jan 2018 11:26:29 GMT

Then we'll need a slightliy different approach:

data preserve;
set have (keep=acc_no agent src branch_f exec trx case_no);
by case_no;
if first.case_no;
run;

data want;
if _n_ = 0 then set have; * just for variable order;
merge
  have (keep=case_no doctype_a tfc)
  preserve
;
by case_no;
run;

The first step is the same, but the second replaces the update with a merge.

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

Kurt_Bremser — Wed, 03 Jan 2018 11:27:51 GMT

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.

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

Astounding — Wed, 03 Jan 2018 17:55:15 GMT

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;

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

Ant_K — Thu, 04 Jan 2018 02:21:35 GMT

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?

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

Ant_K — Thu, 04 Jan 2018 06:47:41 GMT

Hi Kurt,

The 2nd method of using "merge" still didn't work... =(

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

Kurt_Bremser — Thu, 04 Jan 2018 07:43:14 GMT

@Ant_K wrote:

Hi Kurt,

The 2nd method of using "merge" still didn't work... =(

It does work with your example data:

data have;
infile datalines4 dlm=';' dsd;
input
  acc_no :$6.
  agent :$25.
  src :$1.
  branch_f :$2.
  exec :$2.
  trx :$2.
  doctype_a
  tfc :$1.
  case_no :$7.
;
datalines4;
KU1234;Fairview Enterprise;A;KU;KU;KU;2;;ABC6345
KL4321;AMG Marine;X;KL;KL;KL;3;C;ABC6345
KL4321;AMG Marine;X;KL;KL;KL;4;C;ABC6345
KL4322;Macro Trust Fund;X;KL;KL;KL;5;C;ABC6345
;;;;
run;

data preserve;
set have (keep=acc_no agent src branch_f exec trx case_no);
by case_no;
if first.case_no;
run;

data want;
if _n_ = 0 then set have; * just for variable order;
merge
  have (keep=case_no doctype_a tfc)
  preserve
;
by case_no;
run;

proc print data=want noobs;
run;

This result:

                                                                   doctype_
acc_no           agent           src    branch_f    exec    trx        a       tfc    case_no

KU1234    Fairview Enterprise     A        KU        KU     KU         2              ABC6345
KU1234    Fairview Enterprise     A        KU        KU     KU         3        C     ABC6345
KU1234    Fairview Enterprise     A        KU        KU     KU         4        C     ABC6345
KU1234    Fairview Enterprise     A        KU        KU     KU         5        C     ABC6345

exactly matches your wanted result from your initial post.

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

Kurt_Bremser — Thu, 04 Jan 2018 07:45:15 GMT

@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.

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

Ant_K — Thu, 04 Jan 2018 08:40:21 GMT

Hi Kurt,

I just realized what's wrong - i kind of omit part of the "if _n_= 0 then" statement, as i thought it is not significant.

If you don't mind can you explain what does the statement means, as you mentioned "for variable order"?

Thanks again for guiding me on this, really appreciate on your time and effort, as i'm really new in SAS programming and even though i went for the trainings provided by SAS, i guessed experiences of playing around with the coding still counts a lot.

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

Kurt_Bremser — Thu, 04 Jan 2018 08:58:30 GMT

Since this set statement is conditional on _n_ = 0, it will never be executed (_n_ starts with 1 in the first data step iteration and increments from there).

But the set statement also has a declarative character, as the data step compiler reads the dataset's metadata and uses it to set up the PDV (program data vector).

Without this short line, the order of variables in the PDV (and therefore the output dataset) would be determined by the order of datasets in the merge statement, and would look different from what it was in the "have" dataset.

Mind that this is purely cosmetic, the order of variables has nothing to do with their accessibility by name.

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

Ant_K — Thu, 04 Jan 2018 09:17:41 GMT

Thanks Kurt.

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

Astounding — Thu, 04 Jan 2018 12:00:31 GMT

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

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

Ant_K — Fri, 05 Jan 2018 09:35:28 GMT

Hi Kurt,

What if i would like to add in one more condition, where i only want the copying to be on those observations with variable "TFC" not equals to null? Example as below which is expand from first example:

No	Acc_No	Agent	Src	Branch_F	Exec	Trx	DocType_A	TFC	Case No
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

To this:

No	Acc_No	Agent	Src	Branch_F	Exec	Trx	DocType_A	TFC	Case No
1	KU1234	Fairview Enterprise	A	KU	KU	KU	2		ABC6345
2	KU1234	Fairview Enterprise	A	KU	KU	KU	3	C	ABC6345
3	KU1234	Fairview Enterprise	A	KU	KU	KU	4	C	ABC6345
4	KU1234	Fairview Enterprise	A	KU	KU	KU	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

As you can see from the output results, observations 2, 3 & 4 has changed when variable "TFC" is "C", however there are no changes to observations 6 & 7 as "TFC" is null.

Hope you can understand my question.

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

Kurt_Bremser — Fri, 05 Jan 2018 09:50:23 GMT

Please post your "have" dataset as a data step (see my example), so we have something to work with that don't requires making guesses or typing off the screen.

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

Ant_K — Sat, 06 Jan 2018 07:05:33 GMT

Dear Kurt,

I wish i could post up the actual dataset, but because it's company's customer data and it's private and confidential, that is why i made up my own mock data and not the actual dataset.