## Data Manipulation Question?

Solved
Frequent Contributor
Posts: 105

# 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: 6,785

## 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;

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.

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
Posts: 9,599

## 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: 6,785

## 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;

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

Frequent Contributor
Posts: 105

## Re: Data Manipulation Question?

Posted in reply to naveen_srini

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 and locked.

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

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