01-03-2018 02:48 AM
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:
|KL4322||Macro Trust Fund||X||KL||KL||KL||5||C||ABC6345|
To this using SAS:
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.
01-03-2018 03:22 AM
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.
01-03-2018 03:27 AM
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".
01-03-2018 03:38 AM
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;
01-03-2018 03:45 AM
Thanks Kurt, pardon my English and sorry for the confusion.
Thanks for your quick response, i will try it out.
01-03-2018 05:14 AM
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.
01-03-2018 06:26 AM
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.
01-04-2018 02:43 AM
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;
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.
01-04-2018 03:40 AM
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.
01-04-2018 03:47 AM - edited 01-04-2018 03:58 AM
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.
01-05-2018 04:35 AM
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:
|4||KL4322||Macro Trust Fund||X||KL||KL||KL||5||C||ABC6345|
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.
01-05-2018 04:50 AM
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.
01-06-2018 02:05 AM
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.