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
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;
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;
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;
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.
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;
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;
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;
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
%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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.