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

Reply
Occasional Contributor
Posts: 11

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

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_NoAgentSrcBranch_FExecTrxDocType_ATFCCase No
KU1234Fairview EnterpriseAKUKUKU2 ABC6345
KL4321AMG MarineXKLKLKL3CABC6345
KL4321AMG MarineXKLKLKL4CABC6345
KL4322Macro Trust Fund XKLKLKL5CABC6345

 

To this using SAS:

Acc_NoAgentSrcBranch_FExecTrxDocType_ATFCCase No
KU1234Fairview EnterpriseAKUKUKU2 ABC6345
KU1234Fairview EnterpriseAKUKUKU3CABC6345
KU1234Fairview EnterpriseAKUKUKU4CABC6345
KU1234Fairview EnterpriseAKUKUKU5CABC6345

 

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.

Super User
Posts: 9,611

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 11

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

Posted in reply to KurtBremser

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

Super User
Posts: 9,611

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

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 11

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

Posted in reply to KurtBremser

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

 

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

Occasional Contributor
Posts: 11

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

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.

Super User
Posts: 9,611

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 11

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

Posted in reply to KurtBremser

Hi Kurt, 

 

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

Super User
Posts: 9,611

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


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 11

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

Posted in reply to KurtBremser

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.

 

 

Super User
Posts: 9,611

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

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 11

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

Posted in reply to KurtBremser

Thanks Kurt.

Occasional Contributor
Posts: 11

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

Posted in reply to KurtBremser

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: 

 

NoAcc_NoAgentSrcBranch_FExecTrxDocType_ATFCCase No
1KU1234Fairview EnterpriseAKUKUKU2 ABC6345
2KL4321AMG MarineXKLKLKL3CABC6345
3KL4321AMG MarineXKLKLKL4CABC6345
4KL4322Macro Trust Fund XKLKLKL5CABC6345
5SB9564Essentials DotcomBSBSBSB1 CBA2134
6KL3214Visual OpticsXKLKLKL7 CBA2134
7KL3214Visual OpticsXKLKLKL8 CBA2134

 

To this:

 

NoAcc_NoAgentSrcBranch_FExecTrxDocType_ATFCCase No
1KU1234Fairview EnterpriseAKUKUKU2 ABC6345
2KU1234Fairview EnterpriseAKUKUKU3CABC6345
3KU1234Fairview EnterpriseAKUKUKU4CABC6345
4KU1234Fairview EnterpriseAKUKUKU5CABC6345
5SB9564Essentials DotcomBSBSBSB1 CBA2134
6KL3214Visual OpticsXKLKLKL7 CBA2134
7KL3214Visual OpticsXKLKLKL8 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.

Super User
Posts: 9,611

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 11

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

Posted in reply to KurtBremser

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.

 

Ask a Question
Discussion stats
  • 22 replies
  • 282 views
  • 2 likes
  • 4 in conversation