topic Split one row with multiple variables into multiple rows in SAS Programming

Split one row with multiple variables into multiple rows

Feefee — Mon, 12 May 2025 14:37:33 GMT

Hi,

I have dataset that looks like this:

Locker ID	Locations	Person A	Person B	Person C	Person A Code	Person B Code	Person C Code
101	Hampersmith, Silvershine	Sam Copewell	Amy Baker	John Aston	123A	234B	345C
102	Coleridge, Brisbake	Claude Tremaine	Marissa Carter		567D	678E
103	Waterloop, Sturrey	Brian Amble	Charlie Springwater	Mandy Smith	012G	134H	245J

I want to split this data set into multiple rows by the persons like so:

Locker ID	Locations	Person	Person Code
101	Hampersmith, Silvershine	Sam Copewell	123A
101	Hampersmith, Silvershine	Amy Baker	234B
101	Hampersmith, Silvershine	John Aston	345C
102	Coleridge, Brisbake	Claude Tremaine	567D
102	Coleridge, Brisbake	Marissa Carter	678E
103	Waterloop, Sturrey	Brian Amble	012G
103	Waterloop, Sturrey	Charlie Springwater	134H
103	Waterloop, Sturrey	Mandy Smith	245J

Please can you help, all numbers/letters are just placeholders so avoiding code that specifies any of that.

Appreciate your help!

Re: Split one row with multiple variables into multiple rows

PaigeMiller — Mon, 12 May 2025 14:47:44 GMT

/* UNTESTED CODE */

data want;
    set have;
    person=personA;
    personcode=personAcode;
    output;
    person=personB;
    personcode=personBcode;
    output;
    person=personC;
    personcode=personCcode;
    output;
    keep lockerid locations person personcode;
run;

Please from now on provide data as working SAS data step code (examples and instructions). Also, I'm sure you know this, but SAS data sets cannot have variables with spaces in the variable name.

Re: Split one row with multiple variables into multiple rows

data_null__ — Mon, 12 May 2025 15:21:05 GMT

You could use two calls to proc transpose and is pretty easy to extend to more variables.

data have;
   infile cards dsd dlm='09'x;
   input (Locker_ID	Locations	Person_A	Person_B	Person_C	Person_A_Code	Person_B_Code	Person_C_Code)(:$32.);
   cards;
101	Hampersmith, Silvershine	Sam Copewell	Amy Baker	John Aston	123A	234B	345C
102	Coleridge, Brisbake	Claude Tremaine	Marissa Carter	 	567D	678E	 
103	Waterloop, Sturrey	Brian Amble	Charlie Springwater	Mandy Smith	012G	134H	245J
;;;;
   run;

proc transpose data=have out=flip(where=(not missing(col1))) name=vname;
   by locker_id locations;
   var person:;
   run;
proc sort data=flip;
   by locker_id locations vname;
   run;
data flip;
   set flip;
   length _name_ $32;
   pid = first(scan(vname,2,'_'));
   if find(vname,'code','IT') then _name_ = 'Code';
   else _name_ = scan(vname,1,'_');
   run;
proc transpose data=flip out=flop(drop=_name_);
   by locker_id locations pid;
   var col1;
   id _name_;
   run;

Re: Split one row with multiple variables into multiple rows

dxiao2017 — Thu, 22 May 2025 15:52:20 GMT

Hi @Feefee does this answer your question?

data personcd;
   input id location $5-26 person1 $28-42
         person2 $44-62 person3 $64-74 
         p1code $5. p2code $5. p3code $5.;
   datalines;
101 Hampersmith,Silershine Sam Copewell    Amy Baker           John Aston  123A 234B 345C
102 Coleridge,Brisbake     Claude Tremaine Marissa Carter                  567D 678E
103 Waterloop,Sturrey      Brian Amble     Charlie Springwater Mandy Smith 012G 134H 245J
;
run;
proc print data=personcd;run;
proc transpose data=personcd 
               out=name(rename=(col1=person)
                        drop=_name_);
   by id location;
   var person:;
run;
proc print data=name;run;
proc transpose data=personcd 
               out=code(rename=(col1=personcd)
                        drop=_name_);
   by id location;
   var p1code--p3code;
run;
proc print data=code;run;
data final;
   merge name code;
   if person^=' ';
run;
proc print data=final;run;

Re: Split one row with multiple variables into multiple rows

mkeintz — Fri, 23 May 2025 03:21:37 GMT

Probably a single DATA step is the simplest approach. Generalizing a bit from @PaigeMiller, consider the use of arrays, as in:

data want (keep=locker_id locations person code);
  set have;
  array pers {*} person_a--person_c;
  array cods {*} person_a_code-- person_c_code;

  do i=1 to dim(pers);
    person=pers{i};
    code=cods{i};
    if person^=' ' then output;
  end;
run;

If you want to add a PID variable (="A", or "B", or "C", etc.) as done by @data_null__ , you can make a minor tweak:


data want (keep=locker_id locations person pid code);
  set have;
  array pers {*} person_a--person_c;
  array cods {*} person_a_code-- person_c_code;

  do i=1 to dim(pers);
    person=pers{i};
    pid=byte(i+64);   /*Byte(65)='A', Byte(66)='B', etc. */
    code=cods{i};
    if person^=' ' then output;
  end;
run;

Re: Split one row with multiple variables into multiple rows

Kurt_Bremser — Fri, 23 May 2025 08:23:40 GMT

@Feefee wrote:

Hi,

I have dataset that looks like this:

No, you don't. SAS names can't have blanks or linefeeds in them.

Please do ALWAYS post example data as working DATA step code with DATALINES.