topic Re: Base sas coding in New SAS User

Base sas coding

molla — Wed, 02 Jan 2019 13:51:21 GMT

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

Re: Base sas coding

Tom — Wed, 02 Jan 2019 14:22:52 GMT

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?

Re: Base sas coding

cosmid — Wed, 02 Jan 2019 14:36:56 GMT

Your output table doesn't make sense.

Re: Base sas coding

molla — Wed, 02 Jan 2019 14:45:24 GMT

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

Re: Base sas coding

novinosrin — Wed, 02 Jan 2019 14:47:12 GMT

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;

Re: Base sas coding

Astounding — Wed, 02 Jan 2019 14:52:57 GMT

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;

Re: Base sas coding

molla — Wed, 02 Jan 2019 15:04:49 GMT

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.

Re: Base sas coding

Amir — Wed, 02 Jan 2019 15:17:35 GMT

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.

Re: Base sas coding

novinosrin — Wed, 02 Jan 2019 15:19:14 GMT

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;