BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MarkWik
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

5 REPLIES 5
naveen_srini
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Astounding
PROC Star

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.     

naveen_srini
Quartz | Level 8

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

MarkWik
Quartz | Level 8

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.

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
  • 5 replies
  • 771 views
  • 10 likes
  • 4 in conversation