topic Re: transpose data (more than one variable) in SAS Programming

transpose data (more than one variable)

Ccasagran737 — Sat, 04 Jan 2020 22:00:39 GMT

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

Re: transpose data (more than one variable)

novinosrin — Sat, 04 Jan 2020 22:07:59 GMT

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;

Re: transpose data (more than one variable)

novinosrin — Sat, 04 Jan 2020 23:14:55 GMT

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;

Re: transpose data (more than one variable)

Astounding — Sun, 05 Jan 2020 01:01:31 GMT

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.

Re: transpose data (more than one variable)

PGStats — Sun, 05 Jan 2020 03:07:29 GMT

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;

Re: transpose data (more than one variable)

ed_sas_member — Sun, 05 Jan 2020 08:57:26 GMT

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;

Re: transpose data (more than one variable)

Ksharp — Sun, 05 Jan 2020 10:32:50 GMT

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;

Re: transpose data (more than one variable)

Ccasagran737 — Mon, 06 Jan 2020 21:44:06 GMT

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

Re: transpose data (more than one variable)

Ksharp — Tue, 07 Jan 2020 10:40:43 GMT

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