Unpivot a record to multiple records from two pivoted columns

[Suminder]

Hi guys,

I have following dataset

have;

ID      Name           sportsplayed                 languagesSpoke

1        Richard         chess|golf|Sudoku        ENG|HINDI|PUNJABI

2        SAM              judo|golf                       Arabic|ENG

3        Mich              Soccer                          ENG

4        Rach             Cards                            ENG|HINDI

5        Nic                 Cricket|Soccer             ENG

Required Output has to be

want;

ID             NAME                QUALITIES

1               RICHARD          chess

2               RICHARD          golf

3               RICHARD          Sudoku

4               RICHARD          ENG

5               RICHARD          HINDI

6               RICHARD          PUNJABI

...and so on

I know how tp pivot one column...pls help

[ed_sas_member · Accepted solution]

Hi @Suminder 

You can try this code, using the countw() and scan() functions to identify and split words, and then a proc transpose to output the desired dataset. NB: I believe you want to keep one ID per name. If it is not the case, you can sort your dataset by name first and then perform the proc transpose  with "by name" as a BY statement.

Best,

data have;
	input ID Name $ 3-9 sportsplayed $ 11-27  languagesSpoke $ 29-45 ;
	datalines;
1 Richard chess|golf|Sudoku ENG|HINDI|PUNJABI
2 SAM     judo|golf         Arabic|ENG
3 Mich    Soccer            ENG
4 Rach    Cards             ENG|HINDI
5 Nic     Cricket|Soccer    ENG
	;
run;

proc sql noprint;
	select max(countw(sportsplayed,"|")) into:max_nb_sports from have;
	select max(countw(languagesSpoke,"|")) into:max_nb_language from have;
quit;

data have2 (drop=sportsplayed languagesSpoke);
	set have;

	array sports(&max_nb_sports) $;
	array languages(&max_nb_language) $;
	
	name = upcase(name);
	
	do i=1 to &max_nb_sports;
		sports(i) = upcase(scan(sportsplayed,i));
	end;

	do i=1 to &max_nb_language;
		languages(i) = upcase(scan(languagesSpoke,i));
	end;

run;

proc transpose data=have2 out=want (drop=_name_) prefix=QUALITIES;
	var sports: languages:;
	by ID Name;
run;

proc print data=want;
run;

[Tom]

I know how tp pivot one column...pls help

Then why not just convert your two columns into one?

length both $200;
both=catx('|',sportsplayed,languagesSpoke);

And use the method you already know.

[novinosrin]

The word pivot seemed scary and I thought I should give it a pass. I only understand the terminology like transpose/restructuring.

data have;
input ID      Name $           sportsplayed  : $30.             languagesSpoke   : $30. ;
cards;
1        Richard         chess|golf|Sudoku        ENG|HINDI|PUNJABI
2        SAM              judo|golf                       Arabic|ENG
3        Mich              Soccer                          ENG
4        Rach             Cards                            ENG|HINDI
5        Nic                 Cricket|Soccer             ENG
;
data want;
 set have;
 array t(999,999)$30 _temporary_;
 array u(*) sportsplayed languagesspoke;
 do _i=1 to dim(u);
  _k=countw(u(_i),'|');
  do _j=1 to _k;
   t(_i,_j)=scan(u(_i),_j,'|');
  end;
 end;
 do _i=1 to _k;
  do _j=1 to dim(u);
   if t(_j,_i)>' ' then u(_j)=t(_j,_i);
  end;
  output;
 end;
 call missing(of t(*));
 drop _:;
run;