topic Re: Data Manipulation Question? in SAS Programming

Data Manipulation Question?

MarkWik — Wed, 25 Feb 2015 10:32:57 GMT

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

Re: Data Manipulation Question?

naveen_srini — Mon, 02 Mar 2015 08:03:39 GMT

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

Re: Data Manipulation Question?

RW9 — Mon, 02 Mar 2015 09:49:52 GMT

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;

Re: Data Manipulation Question?

Astounding — Mon, 02 Mar 2015 15:54:00 GMT

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;

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.

Re: Data Manipulation Question?

naveen_srini — Wed, 04 Mar 2015 10:58:33 GMT

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

Re: Data Manipulation Question?

MarkWik — Wed, 04 Mar 2015 15:04:48 GMT

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.