BookmarkSubscribeRSS Feed
Ant_K
Fluorite | Level 6

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.

22 REPLIES 22
Ant_K
Fluorite | Level 6

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

Kurt_Bremser
Super User

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;
Ant_K
Fluorite | Level 6

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

 

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

Ant_K
Fluorite | Level 6

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.

Kurt_Bremser
Super User

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.

Ant_K
Fluorite | Level 6

Hi Kurt, 

 

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

Kurt_Bremser
Super User

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

Ant_K
Fluorite | Level 6

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.

 

 

Kurt_Bremser
Super User

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.

Ant_K
Fluorite | Level 6

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.

Ant_K
Fluorite | Level 6

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.

 

Ready to join fellow brilliant minds for the SAS Hackathon?

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!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 22 replies
  • 6633 views
  • 2 likes
  • 4 in conversation