DATA Step, Macro, Functions and more

How to format data on ranges?

Reply
Contributor
Posts: 54

How to format data on ranges?

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;

Super User
Super User
Posts: 7,401

Re: How to format data on ranges?

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;
Super User
Posts: 9,681

Re: How to format data on ranges?

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;
Ask a Question
Discussion stats
  • 2 replies
  • 175 views
  • 0 likes
  • 3 in conversation