transpose data (more than one variable)

[Ccasagran737]

Hi,

I'm trying to solve this problem.

I have this dataset

data have;
	input facility mortgage $ id $ name $;
	datalines;
1 A 001 Jhon
1 B 003 Frank
2 C 004 Tom
;
run;

And I'd like to view my data in this other way

data want;
	infile datalines missover;
	input facility mortgage_1 $ id_1 $ name_1 $ mortgage_2 $ id_2 $ name_2 $ ;
datalines;
1 A 001 Jhon B 003 Frank
2 C 004 Tom
;
run;

I think I have to use "proc transpose" but I don't know how...could someone help me!! Thanks a lot!

Daniele

[novinosrin]

Hi @Ccasagran737 

IDGROUP transpose using Proc summary is slick for this type.

data have;
	input facility mortgage $ id $ name $;
	datalines;
1 A 001 Jhon
1 B 003 Frank
2 C 004 Tom
;
run;


proc sql noprint; 
 select max(obs) into :obs 
from ( select count(*) as obs from have group by facility ) ;
 quit;

proc summary nway data=have missing; 
 class facility; 
 output out = want(drop=_type_ _freq_) 
 idgroup(out[&obs](mortgage id name)=) ;
run;

[novinosrin]

Hello again @Ccasagran737   Perhaps you could be more familiar with the following approach. 

data have;
	input facility mortgage $ id $ name $;
	datalines;
1 A 001 Jhon
1 B 003 Frank
2 C 004 Tom
;
run;

data temp;
 do _n_=1 by 1 until(last.facility);
  set have;
  by facility;
  array t mortgage id name;
  length vn  $32;
  do over t;
  vn=catx('_',vname(t),_n_);
  temp=t;
  output;
  end;
 end;
 keep facility vn temp;
run;

proc transpose data=temp out=want(drop=_name_) ;
by facility;
var temp;
id vn;
run;

[Astounding]

Just a note to consider ...

Most of the time, transposing in this fashion creates more problems than it solves.  Unless you are just intending to print the transposed data, you would usually be better off keeping the data in its current form and learning to program with it in that form.

[PGStats · Accepted solution]

Transform to long, then back to wide, like this:

data have;
	input facility mortgage $ id $ name $;
	datalines;
1 A 001 Jhon
1 B 003 Frank
2 C 004 Tom
;

data temp;
length value $16;
do i = 1 by 1 until(last.facility);
	set have; by facility;
	var = "mortgage"; value = mortgage; output;
	var = "id"; value = id; output;
	var = "name"; value = name; output;
	end;
drop mortgage id name;
run;

proc transpose data=temp out=want delimiter=_;
by facility;
id var i;
var value;
run;

[ed_sas_member]

Hi @Ccasagran737 

Here is another way to achieve this.

proc sql noprint;
	select max(count) into:count
	from (select count(facility) as count from have group by facility);
quit;

data want;
	set have;
	by facility;
	
	/* definition of new columns */
	array mortgage_ (&count) $;
	array id_ (&count) $;
	array name_ (&count) $;
	
	/* reinitialization for each new facility */
	if first.facility then do;
		counter = 0;
		call missing (of mortgage_(*));
		call missing (of id_(*));
		call missing (of name_(*));
	end;
	
	/* calculation of new variables */
	retain mortgage_;
	retain id_;
	retain name_;
	
	counter + 1;

	do i=1 to &count;
		mortgage_(counter) = mortgage;
		id_(counter) = id;
		name_(counter) = name;
	end;
	
	if last.facility then output;
	
	drop i counter mortgage id name;
run;

[Ksharp]

MERGE skill :

data have;
	input facility mortgage $ id $ name $;
	datalines;
1 A 001 Jhon
1 B 003 Frank
2 C 004 Tom
;
run;


data have ;
set have;
by facility;
if first.facility then n=0;
n+1;
run;
proc sql noprint;
select distinct catt('have(where=(n=',n,') rename=
(mortgage=mortgage_',n,' id=id_',n,' name=name_',n,'))')
into : merge separated by ' '
from have;
quit;
data want;
 merge &merge ;
 by facility;
 drop n;
run;

[Ccasagran737]

Hi. I'm studying your program. I don't know this writing ',n,'. Could you help me understand how does it works. Thanks a lot.

Daniele

[Ksharp]

%put &merge ;

could display the code I try to build up .

data want;
 merge

have(where=(n=1) rename=(mortgage=mortgage_1 id=id_1 name=name_1)) have(where=(n=2) rename=(mortgage=mortgage_2 id=id_2 name=name_2))

;
 by facility;
 drop n;
run;