topic Re: How to format data on ranges? in SAS Programming

How to format data on ranges?

angeliquec — Wed, 26 Oct 2016 14:33:06 GMT

Hi,

I have a dataset here whose format I'd like to apply in another dataset. The student belongs on the specified bucket when her/ his score is less than/ equal to the upper, and greater than the lower, except for the bucket with the 100 as upper bound.

Also, the "have" dataset should not be manipulated to get the desired result.

Is this possible?

data have;
input subject $ bucket $ lower upper;
cards;
math bucket1 0 25
math bucket2 25 50
math bucket3 50 75
math bucket4 75 100
math bucket5 100 100
science bucket1 0 50
science bucket2 50 75
science bucket3 75 100
science bucket4 100 100
;
run;

data want;
input name $ subject $ score bucket $;
cards;
john math 24 bucket1
angela math 100 bucket5
alex science 50 bucket1
sophie science 75 bucket2
;
run;

Re: How to format data on ranges?

RW9 — Wed, 26 Oct 2016 14:44:54 GMT

We are talking here about merging data. You could do it with formats, but it might not cover all your requirements. Merging will:

proc sql;
  create table WANT as
  select  A.NAME,
          A.SUBJECT,
          A.SCORE,
          B.BUCKET
  from    STUDENTS A
  left join HAVE B
  on      A.SUBJECT=B.SUBJECT
  and     B.LOWER <= A.SCORE <= B.UPPER;
quit;

Re: How to format data on ranges?

Ksharp — Thu, 27 Oct 2016 03:16:02 GMT

Make it as a format dataset.

 
data have;
input subject $ bucket $ lower upper;
cards;
math bucket1 0 25
math bucket2 25 50
math bucket3 50 75
math bucket4 75 100
math bucket5 100 100
science bucket1 0 50
science bucket2 50 75
science bucket3 75 100
science bucket4 100 100
;
run;
 
data fmt;
 set have(rename=(lower=start upper=end bucket=label subject=fmtname));
run;
proc format cntlin=fmt;
run;

data want;
input name $ subject $ score bucket $;
new_bucket=putn(score,subject);
cards;
john math 24 bucket1
angela math 100 bucket5
alex science 50 bucket1
sophie science 75 bucket2
;
run;