Hi,
From the University table, I need to give score (0 or 1) for Teachers in different category/subcat of different subjects.
How can I transpose the data by adding three new columns (varible, category, subcategory) into the master sheet. The categorization data is available in 'lookup' table.
My desired output (added for one Teacher)
seq | Teacher | Surname | Variable | Category | Subcategory | Trainer | Beginner | Medium | Expert | Mastery | |
1 | Harish | KK | abc@gmail.com | English | Arts | Syllabus1 | 0 | 0 | 1 | 0 | 0 |
1 | Harish | KK | abc@gmail.com | History | Arts | Syllabus1 | 1 | 0 | 0 | 0 | 0 |
1 | Harish | KK | abc@gmail.com | Geography | Arts | Syllabus2 | 1 | 0 | 0 | 0 | 0 |
1 | Harish | KK | abc@gmail.com | Math | Development | Syllabus3 | 0 | 1 | 0 | 0 | 0 |
1 | Harish | KK | abc@gmail.com | Science | Development | Syllabus4 | 0 | 0 | 0 | 1 | 0 |
1 | Harish | KK | abc@gmail.com | Economics | Emerging | Syllabus1 | 0 | 0 | 1 | 0 | 0 |
1 | Harish | KK | abc@gmail.com | IT | Emerging | Syllabus2 | 1 | 0 | 0 | 0 | 0 |
1 | Harish | KK | abc@gmail.com | Civics | Emerging | Syllabus3 | 0 | 0 | 0 | 0 | 1 |
1 | Harish | KK | abc@gmail.com | Other | Emerging | Syllabus2 | 1 | 0 | 0 | 0 | 0 |
data University;
length Teacher $ 10 email $20;
input seq $ Teacher $ Surname $ Email $ English $ History $ Geography $ Math $ Science $ Economics $ IT $ Civics $ Other $;
cards;
1 Aristotle KK abc@gmail.com Medium Trainer Trainer Beginner Expert Medium Trainer Mastery Trainer
2 Vem KK1 abc@gmail.com Trainer Beginner Trainer Mastery Trainer Medium Medium Expert Beginner
3 Charm KK2 abc@gmail.com Medium Trainer Beginner Trainer Trainer Medium Trainer Trainer Trainer
4 Newton KK3 abc@gmail.com Trainer Beginner Beginner Mastery Trainer Medium Medium Trainer Trainer
5 Grammy KK4 abc@gmail.com Beginner Expert Trainer Expert Trainer Medium Trainer Expert Trainer
6 Rambo KK5 abc@gmail.com Trainer Beginner Trainer Trainer Trainer Trainer Trainer Trainer Mastery
7 Nikki KK6 abc@gmail.com Medium Medium Expert Expert Trainer Trainer Trainer Trainer Trainer
8 Leo KK7 abc@gmail.com Mastery Trainer Trainer Trainer Trainer Mastery Trainer Medium Trainer
9 Fishcer KK8 abc@gmail.com Trainer Trainer Trainer Trainer Trainer Expert Trainer Expert Trainer
;
run;
data lookup;
length variab $10 Category Sub_Category $20 ;
input variab $ Category $ Sub_Category $;
cards;
English Arts Syllabus1
History Arts Syllabus1
Geography Arts Syllabus2
Math Development Syllabus3
Science Development Syllabus4
Economics Emerging Syllabus1
IT Emerging Syllabus2
Civics Emerging Syllabus3
Other Emerging Syllabus2
;run;
proc sort data=university; by Teacher;run;
proc sort data=lookup;by variab;run;
See this:
data University;
input seq $ Teacher :$10. Surname $ Email :$20. English $ History $ Geography $ Math $ Science $ Economics $ IT $ Civics $ Other $;
cards;
1 Aristotle KK abc@gmail.com Medium Trainer Trainer Beginner Expert Medium Trainer Mastery Trainer
2 Vem KK1 abc@gmail.com Trainer Beginner Trainer Mastery Trainer Medium Medium Expert Beginner
3 Charm KK2 abc@gmail.com Medium Trainer Beginner Trainer Trainer Medium Trainer Trainer Trainer
4 Newton KK3 abc@gmail.com Trainer Beginner Beginner Mastery Trainer Medium Medium Trainer Trainer
5 Grammy KK4 abc@gmail.com Beginner Expert Trainer Expert Trainer Medium Trainer Expert Trainer
6 Rambo KK5 abc@gmail.com Trainer Beginner Trainer Trainer Trainer Trainer Trainer Trainer Mastery
7 Nikki KK6 abc@gmail.com Medium Medium Expert Expert Trainer Trainer Trainer Trainer Trainer
8 Leo KK7 abc@gmail.com Mastery Trainer Trainer Trainer Trainer Mastery Trainer Medium Trainer
9 Fishcer KK8 abc@gmail.com Trainer Trainer Trainer Trainer Trainer Expert Trainer Expert Trainer
;
data lookup;
length variab $10 Category Sub_Category $20 ;
input variab $ Category $ Sub_Category $;
cards;
English Arts Syllabus1
History Arts Syllabus1
Geography Arts Syllabus2
Math Development Syllabus3
Science Development Syllabus4
Economics Emerging Syllabus1
IT Emerging Syllabus2
Civics Emerging Syllabus3
Other Emerging Syllabus2
;
proc transpose
data=university
out=levels
;
by seq;
var english--other;
run;
data levels2;
set levels;
value = 1;
run;
proc transpose
data=levels2
out=wide (rename=(_name_=variable))
;
by seq _name_ notsorted;
var value;
id col1;
run;
data want;
merge
university (keep=seq teacher surname email)
wide
;
by seq;
length Category Sub_Category $20;
if _n_ = 1
then do;
declare hash l (dataset:"lookup (rename=(variab=variable))");
l.definekey("variable");
l.definedata("category","sub_category");
l.definedone();
end;
array nums {*} _numeric_;
do _n_ = 1 to dim(nums);
nums{_n_} = coalesce(nums{_n_},0);
end;
if l.find() ne 0
then do;
category = "";
sub_category = "";
end;
run;
You might want to add a RETAIN statement for the simple purpose of ordering the variables in the last data step.
I don't follow this logic. Why is Trainer=1 and the rest 0 in the second obs?
If the goal is to get all of the values from Lookup attached to every record from University:
Proc sql; create table want as select a.*, b.* from university as a, lookup as b ; quit;
This is a Cartesian join where all records from one set are paired with all records from another set.
There will be a note in the log about such. If your sets are large this can take a fair amount of time.
See this:
data University;
input seq $ Teacher :$10. Surname $ Email :$20. English $ History $ Geography $ Math $ Science $ Economics $ IT $ Civics $ Other $;
cards;
1 Aristotle KK abc@gmail.com Medium Trainer Trainer Beginner Expert Medium Trainer Mastery Trainer
2 Vem KK1 abc@gmail.com Trainer Beginner Trainer Mastery Trainer Medium Medium Expert Beginner
3 Charm KK2 abc@gmail.com Medium Trainer Beginner Trainer Trainer Medium Trainer Trainer Trainer
4 Newton KK3 abc@gmail.com Trainer Beginner Beginner Mastery Trainer Medium Medium Trainer Trainer
5 Grammy KK4 abc@gmail.com Beginner Expert Trainer Expert Trainer Medium Trainer Expert Trainer
6 Rambo KK5 abc@gmail.com Trainer Beginner Trainer Trainer Trainer Trainer Trainer Trainer Mastery
7 Nikki KK6 abc@gmail.com Medium Medium Expert Expert Trainer Trainer Trainer Trainer Trainer
8 Leo KK7 abc@gmail.com Mastery Trainer Trainer Trainer Trainer Mastery Trainer Medium Trainer
9 Fishcer KK8 abc@gmail.com Trainer Trainer Trainer Trainer Trainer Expert Trainer Expert Trainer
;
data lookup;
length variab $10 Category Sub_Category $20 ;
input variab $ Category $ Sub_Category $;
cards;
English Arts Syllabus1
History Arts Syllabus1
Geography Arts Syllabus2
Math Development Syllabus3
Science Development Syllabus4
Economics Emerging Syllabus1
IT Emerging Syllabus2
Civics Emerging Syllabus3
Other Emerging Syllabus2
;
proc transpose
data=university
out=levels
;
by seq;
var english--other;
run;
data levels2;
set levels;
value = 1;
run;
proc transpose
data=levels2
out=wide (rename=(_name_=variable))
;
by seq _name_ notsorted;
var value;
id col1;
run;
data want;
merge
university (keep=seq teacher surname email)
wide
;
by seq;
length Category Sub_Category $20;
if _n_ = 1
then do;
declare hash l (dataset:"lookup (rename=(variab=variable))");
l.definekey("variable");
l.definedata("category","sub_category");
l.definedone();
end;
array nums {*} _numeric_;
do _n_ = 1 to dim(nums);
nums{_n_} = coalesce(nums{_n_},0);
end;
if l.find() ne 0
then do;
category = "";
sub_category = "";
end;
run;
You might want to add a RETAIN statement for the simple purpose of ordering the variables in the last data step.
Try this
data want;
set University;
do i=1 to n;
set lookup point=i nobs=n;
array a Trainer Beginner Medium Expert Mastery;
do over a;
a = 0;
if vname(a) = vvaluex(variab) then a=1;
end;
output;
end;
keep seq Teacher Surname Email Variable Category Subcategory Trainer Beginner Medium Expert Mastery;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.