Hi Friends,
I need help with designing the below code.
Consider the table below. I need to derive the last column.
The only way that i can do that is.
I have multiple groups as given below and each group has different ID's associated with them.
The interest rate of different groups are given. To derive the NEW interest rate, i need to select INTEREST RATE OF only those ID's that have a value of 300 or 500 (a group can have either 300 or 500 value). The Interest rate of either 300 or 500 ID becomes the NEW rate of that group.
I am not able to code this logic. Please help
Group | ID | Interest rate | TO BE DERIVED: New Rate |
x | 300 | 12 | 12 |
700 | 1 | 12 | |
701 | 2 | 12 | |
702 | 3 | 12 | |
703 | 3 | 12 | |
704 | 4 | 12 | |
y | 300 | 15 | 15 |
700 | 4 | 15 | |
701 | 5 | 15 | |
702 | 6 | 15 | |
703 | 7 | 15 | |
704 | 6 | 15 | |
z | 500 | 23 | 23 |
700 | 4 | 23 | |
701 | 3 | 23 | |
702 | 2 | 23 | |
703 | 1 | 23 | |
704 | 4 | 23 | |
z1 | 500 | 20 | 20 |
700 | 5 | 20 | |
701 | 8 | 20 | |
702 | 9 | 20 | |
703 | 6 | 20 | |
704 | 3 | 20 |
Since:
then you can make your task into a simple one-to-many merge:
data want;
merge have (keep=group id interest_rate where=(id=300 or id=500) rename=interest_rate=new_rate)
have ;
by group;
run;
BTW, this allows the ID=300/500 record to fall anywhere in its group - it doesn't have to be first.
Let SAS do the work.
Postscript: Added "group" to the "keep=" parameter.
Hi Friends,
I need help with designing the below code.
Consider the table below. I need to derive the last column.
The only way that i can do that is.
I have multiple groups as given below and each group has different ID's associated with them.
The interest rate of different groups are given. To derive the NEW interest rate, i need to select INTEREST RATE OF only those ID's that have a value of 300 or 500 (a group can have either 300 or 500 value). The Interest rate of either 300 or 500 ID becomes the NEW rate of that group.
I am not able to code this logic. Please help
Group | ID | Interest rate | TO BE DERIVED: New Rate |
x | 300 | 12 | 12 |
700 | 1 | 12 | |
701 | 2 | 12 | |
702 | 3 | 12 | |
703 | 3 | 12 | |
704 | 4 | 12 | |
y | 300 | 15 | 15 |
700 | 4 | 15 | |
701 | 5 | 15 | |
702 | 6 | 15 | |
703 | 7 | 15 | |
704 | 6 | 15 | |
z | 500 | 23 | 23 |
700 | 4 | 23 | |
701 | 3 | 23 | |
702 | 2 | 23 | |
703 | 1 | 23 | |
704 | 4 | 23 | |
z1 | 500 | 20 | 20 |
700 | 5 | 20 | |
701 | 8 | 20 | |
702 | 9 | 20 | |
703 | 6 | 20 | |
704 | 3 | 20 |
300/500 also seem to be the lowest values in the data set. Will that always hold true?
If so, something like this works:
Note that I'm assuming that your group identifier is actually repeated across each row, not blank as in your sample data.
data want;
set have;
by group;
retain new_rate;
if first.ID then new_rate = interest_rate;
run;
If it was blank....
data want;
set have;
retain new_rate;
if not missing(Group) then new_rate = interest_rate;
run;
@Rhino84 wrote:
Hi Friends,
I need help with designing the below code.
Consider the table below. I need to derive the last column.
The only way that i can do that is.
I have multiple groups as given below and each group has different ID's associated with them.
The interest rate of different groups are given. To derive the NEW interest rate, i need to select INTEREST RATE OF only those ID's that have a value of 300 or 500 (a group can have either 300 or 500 value). The Interest rate of either 300 or 500 ID becomes the NEW rate of that group.
I am not able to code this logic. Please help
Group ID Interest rate TO BE DERIVED: New Rate x 300 12 12 700 1 12 701 2 12 702 3 12 703 3 12 704 4 12 y 300 15 15 700 4 15 701 5 15 702 6 15 703 7 15 704 6 15 z 500 23 23 700 4 23 701 3 23 702 2 23 703 1 23 704 4 23 z1 500 20 20 700 5 20 701 8 20 702 9 20 703 6 20 704 3 20
I don't know what your "100" level records look like, but try running your data through something like this, below. You don't have to run the first Data step if already have a SAS data set.
Jim
DATA Have;
LENGTH Group $2;
LENGTH ID $3;
LENGTH Interest_Rate 8.;
RETAIN Group ' ';
INFILE Datalines DSD DLM='09'X MISSOVER;
INPUT Group $
ID $
Interest_Rate
;
DATALINES;
x 300 12
700 1
701 2
702 3
703 3
704 4
y 300 15
700 4
701 5
702 6
703 7
704 6
z 500 23
700 4
701 3
702 2
703 1
704 4
z1 500 20
700 5
701 8
702 9
703 6
704 3
;
RUN;
DATA Want;
RETAIN _Prior_Group ' ';
DROP _Prior_Group;
SET Have;
RETAIN New_Rate 0;
IF MISSING(Group) THEN
Group = _Prior_Group;
_Prior_Group = Group;
IF ID = '300' OR
ID = '500' THEN
New_Rate = Interest_Rate
;
RUN;
You have a few answers now that will solve this issue but in general, this means your example data is not reflective of your actual situation. Make sure to spend some more time ensuring your examples match your actual data.
@Rhino84 wrote:
300 0r 500 is the second value in a group, it comes after 100,(300 or 500), 700,701,702,703,704 and 705 is the correct order and the vale of new column can be either 300 or 500 and it will replicate for all ID's.
Please help.I have been stuck on this for 3 days
Since:
then you can make your task into a simple one-to-many merge:
data want;
merge have (keep=group id interest_rate where=(id=300 or id=500) rename=interest_rate=new_rate)
have ;
by group;
run;
BTW, this allows the ID=300/500 record to fall anywhere in its group - it doesn't have to be first.
Let SAS do the work.
Postscript: Added "group" to the "keep=" parameter.
The below table reflects the correct data.
The below query helps me to make the new rate same as interest rate. But i want all the corresponding rows in New rate be same as Interest rate of 300 or 500 to be same within each single group.
Group | ID | Interest rate | TO BE DERIVED: New Rate |
x | 300 | 12 | 12 |
x | 700 | 1 | |
x | 701 | 2 | |
x | 702 | 3 | |
x | 703 | 3 | |
x | 704 | 4 | |
y | 300 | 15 | 15 |
y | 700 | 4 | |
y | 701 | 5 | |
y | 702 | 6 | |
y | 703 | 7 | |
y | 704 | 6 | |
z | 500 | 23 | 23 |
z | 700 | 4 | |
z | 701 | 3 | |
z | 702 | 2 | |
z | 703 | 1 | |
z | 704 | 4 | |
z1 | 500 | 20 | 20 |
z1 | 700 | 5 | |
z1 | 701 | 8 | |
z1 | 702 | 9 | |
z1 | 703 | 6 | |
z1 | 704 | 3 |
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.