o rowsid home_addr bill_addr work_addr
1 abc street
2 pqr street mno street
3 Main st zxc street Downtown
how to make them look like this:
id Addr
1 abc street
2 pqr street
2 mno street
3 Main street
3 Zxc street
3 Downtown
proc sort data=have;
by id;
run;
proc transpose data=BOOK12 out=have1(drop=_name_);
by id;
var home_addr bill_addr work_addr;
run;
data want;
set have1;
if col1='' then delete;
rename col1=Addr ;
run;
id | Addr |
1 | abc street |
2 | pqr street |
2 | mno street |
3 | Main st |
3 | zxc street |
3 | Downtown |
Alternatively by arrays
data want(where=(addr ne ''));
set have;
array cha(3) $ ("home_addr","bill_addr","work_addr");
array new(3) $ home_addr bill_addr work_addr;
do i = 1 to dim(cha);
variable=cha(i);
Addr=new(i);
output;
end;
keep rowsid addr;
run;
Thanks,
Jagadish
whats the variable in here?
The variable "variable" here has variables (home_addr,bill_addr,work_addr) as values.
I mean the horizontal variables are converted to the values in the column variable.
Thanks,
Jagadish
How about?:
proc transpose data=have out=want
(rename=(col1=addr rowsid=id)
where=(not missing(addr))
drop=_:);
by rowsid notsorted;
var home_addr bill_addr work_addr;
run;
While you have already received good advice ... if you would prefer a simple approach, here is one way:
data want;
set have;
if home_addr > ' ' then do;
addr = home_addr;
id = rowsid;
output;
end;
if work_addr > ' ' then do;
addr = work_addr;
id = rowsid;
output;
end;
if bill_addr > ' ' then do;
addr = bill_addr;
id = rowsid;
output;
end;
keep id addr;
run;
Here's a data step solution:
data want;
set have;
*Declare array of all addresses;
array addr(3) $ home_add work_addr bus_addr;
*loop over address to output;
do i=1 to dim(addr);
*if address is not missing then assign to variable and output;
if addr(i) ne " " then do;
address=addr(i);
output;
end; *end do;
end; *end do loop;
keep rowid address;
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.