topic Re: Transpose the table and insert few column by look up in SAS Programming

Transpose the table and insert few column by look up

dash — Fri, 20 Nov 2020 09:28:39 GMT

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	Email	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;

Re: Transpose the table and insert few column by look up

dash — Fri, 20 Nov 2020 09:34:03 GMT

Sorry for my mistake in desired output where Teacher "Abraham" replace name "Harish".

Re: Transpose the table and insert few column by look up

PeterClemmensen — Fri, 20 Nov 2020 10:05:09 GMT

I don't follow this logic. Why is Trainer=1 and the rest 0 in the second obs?

Re: Transpose the table and insert few column by look up

ballardw — Fri, 20 Nov 2020 10:06:24 GMT

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.

Re: Transpose the table and insert few column by look up

Kurt_Bremser — Fri, 20 Nov 2020 10:10:06 GMT

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.

Re: Transpose the table and insert few column by look up

PeterClemmensen — Fri, 20 Nov 2020 10:16:44 GMT

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;