Help me in getting the below output
Table 1
Id Per
1 10
1 20
2 60
1 90
Table 2
Id range1 range2 Rating
1 10 20 1
1 21 40 2
1 41 60 3
2 10 20 1
2 21 60 2
2 61 90 3
Output
Id Per rating
1 10 1
1 20 1
2 60 2
1 90 3
Similary I have many IDs and their ranges and rating
I need help in coding this with out hardcoding
What did you try? Can you explain in words what the two input tables are? In particular the ID variable seems confusing to me. At first I thought it was just identifying different subjects in the first table, but then it appears to be important in the second table also. Perhaps it is more of an id of which rating system you want to use?
Also how do you get the last two results? For ID=2 and PER=60 why is the result 2 and not 3? For ID=1 and PER=90 why is the result 3 and not missing?
If Id in both tables matches we need to check the per column in the table1
With the range1 and range2 ,accor accord we need to give the rating as defined in table 2
Your output table doesn't make sense.
Hi @molla Like others pointed out, Your output sample isn't clear, that leads us to assumptions.
1 90 3 /*this should be missing*/
data one;
input Id Per;
cards;
1 10
1 20
2 60
1 90
;
data two;
input Id range1 range2 Rating;
cards;
1 10 20 1
1 21 40 2
1 41 60 3
2 10 20 1
2 21 60 2
2 61 90 3
;
proc sql;
create table want as
select a.*,b.rating
from one a left join two b
on a.id=b.id and (a.per=b.range1 or a.per=b.range2)
order by id,per;
quit;
Yes it should be missing,it's my mistake,
And the per value will not be the the boundaries of the range values,
It may be in between the range1 and range2
Like 23,44.4...in this cases also it should the proper rating
The ids also increase up to 10 and the corresponding per and rating.
@
Hi,
Does the following help?
data have1;
input id per;
datalines;
1 10
1 20
2 60
1 90
;
data have2;
input id range1 range2 rating;
datalines;
1 10 20 1
1 21 40 2
1 41 60 3
2 10 20 1
2 20 60 2
2 61 90 3
;
/* use sql to lookup data */
proc sql noprint;
create table
want as
select
have1.*
,rating
from
have1 h1
left join
have2 h2
on
h1.id eq h2.id
and per between range1 and range2
;
quit;
Regards,
Amir.
Ok just a slight tweak
data one;
input Id Per;
cards;
1 10
1 20
2 60
1 90
;
data two;
input Id range1 range2 Rating;
cards;
1 10 20 1
1 21 40 2
1 41 60 3
2 10 20 1
2 21 60 2
2 61 90 3
;
proc sql;
create table want as
select a.*, rating
from one a left join two b
on a.id=b.id and (b.range1<=a.per<=b.range2)
group by a.id,per
order by id,per;
quit;
Step 1: create a format based on TABLE2:
data make_format;
set table2;
fmtname='$idrange';
start = put(id, z3.) || put(range1, z3.);
end = put(id, z3.) || put(range2, z3.);
keep fmtname start end;
run;
The use of the z3 format is assuming that your ID values and range values are limited to no more than 3 digits. And the solution is assuming that ID is a numeric variable. If the real data is different, that can still be programmed with small changes. But since the post doesn't mention anything about the characteristics of the variables, I had to start somewhere by making some assumptions.
proc format cntlin=make_format;
run;
Step 2: Use the format to assign RATING:
data want;
set table1;
rating = input( put( put(id, z3.) || put(per, z3.) , $idrange.), 3.);
run;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.