Base sas coding

[molla]

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

[Tom]

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?

[molla]

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

[cosmid]

Your output table doesn't make sense.

[novinosrin]

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;

[molla]

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.

@

[Amir]

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.

[novinosrin]

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;

[Astounding]

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;