BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8

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 

7 REPLIES 7
Mit
Calcite | Level 5 Mit
Calcite | Level 5

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

Jagadishkatam
Amethyst | Level 16

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

Thanks,
Jag
SASPhile
Quartz | Level 8

whats the variable in here?

Jagadishkatam
Amethyst | Level 16

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

Thanks,
Jag
art297
Opal | Level 21

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;

Astounding
PROC Star

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;


Reeza
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1117 views
  • 0 likes
  • 6 in conversation