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.
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.
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".
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;
Thanks Kurt, pardon my English and sorry for the confusion.
Thanks for your quick response, i will try it out.
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.
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.
Hi Kurt,
The 2nd method of using "merge" still didn't work... =(
@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.
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.
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.
Thanks Kurt.
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.
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.
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: