Data Manipulation Question?

Accepted Solution Solved
Reply
Contributor
Posts: 72
Accepted Solution

Data Manipulation Question?

HAVE

a  b c d e  f g h  i J
1 1 2 1 2 3 1 2 3 4

WANT


a  b c d
1  
1 2 
1 2 3
1 2 3 4


Accepted Solutions
Solution
‎03-02-2015 10:54 AM
Super User
Posts: 5,083

Re: Data Manipulation Question?

Mark,

I believe this is a trickier problem than it appears at first glance, so here's my stab at it.

data want;

   set have;

   array old {10} a b c d e f g h i j;

   array new {10} new1-new10;

   do _i_=1 to 10;

      add_next=1;

      do _k_=1 to 10;

         if old{_k_} = new{_i_} then add_next=0;

      end;

      if add_next=1 then do;

          next_var + 1;

          new{next_var} = old{_i_};

          output;

      end;

   end;

   keep new1-new10;

   rename new1=a new2=b new3=c new4=d new5=e new6=f new7=g new8=h new9=i new10=j;

run;
 

It's a headache to look at, and is untested at this point.  But it should give you what you need.

Good luck.     

View solution in original post


All Replies
Frequent Contributor
Posts: 115

Re: Data Manipulation Question?

Hi,  The code below does what you want:

data want ( keep =  a--d ) ;

            infile datalines dsd truncover ;

            array k (4)a b c d ;

            n + 1 ;                    

            if n > 4 then n = 1 ;

            if n = 1 then

            do ;

               m + 1 ;                 

               if _n_ > 1 then input ;

            end ;

            do i = 1 to n ;

               input k ( i ) @@ ;

            end ;

         datalines ;

         1,1,2,1,2,3,1,2,3,4

        run ;

HTH,

Naveen

L&T Infotech

Super User
Super User
Posts: 7,401

Re: Data Manipulation Question?

Hi,

It simply a matter of setting the table four times, keeping the data you want and renaming the columns:

data have;

a=1;  b=1; c=2; d=1; e=2;  f=3; g=1; h=2;  i=3; J=4; output;

run;

data want;

  set have (keep=a)

      have (keep=b c rename=(b=a c=b))

      have (keep=d e f rename=(d=a e=b f=c))

      have (keep=g h i j rename=(g=a h=b i=c j=d));

run;

Solution
‎03-02-2015 10:54 AM
Super User
Posts: 5,083

Re: Data Manipulation Question?

Mark,

I believe this is a trickier problem than it appears at first glance, so here's my stab at it.

data want;

   set have;

   array old {10} a b c d e f g h i j;

   array new {10} new1-new10;

   do _i_=1 to 10;

      add_next=1;

      do _k_=1 to 10;

         if old{_k_} = new{_i_} then add_next=0;

      end;

      if add_next=1 then do;

          next_var + 1;

          new{next_var} = old{_i_};

          output;

      end;

   end;

   keep new1-new10;

   rename new1=a new2=b new3=c new4=d new5=e new6=f new7=g new8=h new9=i new10=j;

run;
 

It's a headache to look at, and is untested at this point.  But it should give you what you need.

Good luck.     

Frequent Contributor
Posts: 115

Re: Data Manipulation Question?

Hi Mark, Sorry I didn't realise you were reading from a SAS dataset as RW9's example or @Astounding 's example illustrates.  Here is also a way:

data have;
input a b c d e f g h i j;
datalines;
1 1 2 1 2 3 1 2 3 4
;

data restruct;
set have;
array k _numeric_;
do l=1 to dim(k);
new=k(l);
r=lag(new);
if new=1 then group+1;
output;
end;
keep new r group;
run;


data want;
array var(4)a b c d;
call missing (of var{*});
do until (last.group);
set restruct;
by group;
if new ne 1 then var(r)= r;
var(sum(r,1))=new;
end;
keep a b c d;
run;

Regards,

Naveen Srinivasan

L&T Infotech

Contributor
Posts: 72

Re: Data Manipulation Question?

Thank you @Astounding @naveen_srini  @RW9 for helping me out. @Naveen: your answers gives the exact wanted output and a very well thought out logic. But Robert Virgile's works more efficiently although requires little more tweaking to match the exact wanted output. I fancy @Astounding's code that does it one pass and would mark his as correct, nevertheless many many thanks Naveen and RW for the code help.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 378 views
  • 10 likes
  • 4 in conversation