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;
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.