topic Unpivot a record to multiple records from two pivoted columns in SAS Programming

Unpivot a record to multiple records from two pivoted columns

Suminder — Fri, 29 Nov 2019 20:03:51 GMT

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

Re: Unpivot a record to multiple records from two pivoted columns

ed_sas_member — Fri, 29 Nov 2019 21:09:24 GMT

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;

Re: Unpivot a record to multiple records from two pivoted columns

Tom — Fri, 29 Nov 2019 21:33:49 GMT

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.

Re: Unpivot a record to multiple records from two pivoted columns

novinosrin — Fri, 29 Nov 2019 21:38:09 GMT

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;