topic Re: re Replace value within ID if condition satisfied DESCRIPTION =: 'Intnal'; in SAS Programming

re Replace value within ID if condition satisfied

twildone — Tue, 11 Sep 2018 23:36:11 GMT

Hi.....I am trying to replace the entry of "International" in the description field if there is an entry of either 'Intnal - BAA', 'Intnal - CA', 'Intnal - AM' or 'Intnal - MOA' appears for the same ID with either 'Business Administrative Assistant', 'Carpentry', 'Auto Mechanics' or 'Medical Office Assistant'. Any suggestions...thanks in adavance.

data have;
input id description;
cards;
1 International
1 Seat Deposit
1 Intnal - BAA
2 International
2 Admin Fee
2 Seat Deposit
2 Intnal - CA
3 International
3 Intnal – AM
4 International
4 Admin Fee
4 Seat Deposit
4 Lab Fee
4 Text Book
4 Intnal - MAA
;

Want;

1 Business Administrative Assistant

1 Seat Deposit

1 Intnal - BAA

2 Carpentry

2 Admin Fee

2 Seat Deposit

2 Intnal - CA

3 Auto Mechanics

3 Intnal – AM

4 Medical Office Assistant

4 Admin Fee

4 Seat Deposit

4 Lab Fee

4 Text Book

4 Intnal - MOA

Re: re Replace value within ID if condition satisfied

ChrisNZ — Wed, 12 Sep 2018 00:08:06 GMT

Like this?

data HAVE;
  input ID DESCRIPTION :& $50.;
cards;
1 International
1 Seat Deposit
1 Intnal - BAA
2 International
2 Admin Fee
2 Seat Deposit
2 Intnal - CA
3 International
3 Intnal - AM 
4 International
4 Admin Fee
4 Seat Deposit
4 Lab Fee
4 Text Book
4 Intnal - MOA
run;
data _F;
  retain TYPE 'N' FMTNAME 'map'; 
  set HAVE;
  where DESCRIPTION =: 'Intnal';
  START=ID; LABEL=DESCRIPTION;
  if ID ne lag(ID);
run;
proc format cntlin=_F; 
  value $remap 'Intnal - BAA' ='Business Administrative Assistant'
               'Intnal - CA ' ='Carpentry'
               'Intnal - AM ' ='Auto Mechanics'
               'Intnal - MOA' ='Medical Office Assistant';
run;
data WANT;
  set HAVE;
  if DESCRIPTION='International' then DESCRIPTION=put(put(ID,map. -l),$remap.);
run;

SAS Output

ID	DESCRIPTION
1	Business Administrative Assistant
1	Seat Deposit
1	Intnal - BAA
2	Carpentry
2	Admin Fee
2	Seat Deposit
2	Intnal - CA
3	Auto Mechanics
3	Intnal - AM
4	Medical Office Assistant
4	Admin Fee
4	Seat Deposit
4	Lab Fee
4	Text Book
4	Intnal - MOA

Re: re Replace value within ID if condition satisfied

twildone — Wed, 12 Sep 2018 13:25:22 GMT

Hi Chris....thanks for your suggestion. I tried your suggestion and I am getting the following error message:

ERROR: For format MAP, this range is repeated, or values overlap: 157796-157796.

Re: re Replace value within ID if condition satisfied DESCRIPTION =: 'Intnal';

ChrisNZ — Wed, 12 Sep 2018 22:30:54 GMT

It probably means that the same ID appears twice in table _F, which means that the input table is not grouped by ID.

I assumed that the IDs were grouped, and that there could only be one "job" for each in case of replacement of the international values (there is no exception flagging for multiple values in the code).

1. If it's just a matter of duplicate ID with the same "job" mapping (because IDs were not grouped in the original file), you can simply deduplicate table _F

2. If the same ID maps to multiple jobs (and again I do not flag this in the code), you also need to deduplicate by ID, and pick one the the "jobs".

The line

if ID ne lag(ID);

keeps only one row for each ID if they are grouped together.

If they are not grouped, you need to do this task as an additional step.

Note that if this test ever used, it means that multiple IDs had DESCRIPTION =: 'Intnal' , in which case one might want to look at whether they map to the same "job" (the test just keeps the first one found).

Does this make sense?

Re: re Replace value within ID if condition satisfied DESCRIPTION =: 'Intnal';

twildone — Thu, 13 Sep 2018 00:22:04 GMT

Hi Chris.....yes it makes sense and your right there are duplicates. The data is actually accounting data where the description field may have the same entries such as Tuition Payment where the student may have made tuition payments as installment payment on different dates so there would be multiple records to record each of the payments as Tuition Payment.