topic Re: Collapsing Data (multiple lines to one) in SAS Procedures

Collapsing Data (multiple lines to one)

drjohn61 — Wed, 24 Dec 2014 22:05:36 GMT

I have a data set that has multiple lines per record that I want to collapse into one. The problem is the following:

Each record has a 'FormID' (FID) that is the main key.

Each line for the FormID has a 'Parent' (P) and a 'Child' (C) field.

The uppermost 'level' is the Parent with no Child (Child is Null).

Each Parent can have multiple Children.

Each Child can have Grandchildren (G1C) ...which can have Great-Grandchildren (G2C)...and so on.

There is no set number of Children, Grandchildren, etc... per Parent.

The data look like this:

FormID Parent# Child#

1 1

1 1 2

1 2 20

1 20 200

1 1 3

1 3 30

1 30 300

1 300 3000

In the above case, the records would collapse as follows:

FID-1 P-1 C-2 G1C-20 G2C-200 G3C-.

FID-1 P-1 C-3 G1C-30 G2C-300 G3C-3000

In other words, where the Child# becomes the Parent# for the same FormID, that signifies the next level down. I am trying to figure out how to dynamically determine the number of levels and bring the lines up to the appropriate FormID/Parent/Child/Grandchild/etc. combination.

I have hardcoded it to a G3C level by left joining any combo of FID and P/C/GC/G1C/G2C/G3C combo, since, currently, there are only G2Cs in the set, but that could change at any time...and is really inefficient...

Any help would be appreciated.

Thanks,

John L.

Re: Collapsing Data (multiple lines to one)

Astounding — Thu, 25 Dec 2014 01:18:52 GMT

Just bit of help, at least I hope it turns out that way. When you bring the data into SAS, you have to select what the variable names will be and they have to remain the same from one observation to the next.

So it would help if you could describe the final outcome based on your sample data, supplying both the variable names and their values for this sample. Any additional description couldn't hurt, but that would be the starting point.

Most likely, the first step would involve a program that calculates the maximum number of children, grandchildren, etc. needed for your particular incoming data.

Good luck.

Re: Collapsing Data (multiple lines to one)

Reeza — Thu, 25 Dec 2014 02:00:19 GMT

Do you have SAS/OR? Sounds like a BOM problem to some degree.

Re: Collapsing Data (multiple lines to one)

FriedEgg — Thu, 25 Dec 2014 03:41:14 GMT

I agree with Reeza about BOM on this one.

data Forms;

input FormID Parent Child @@;

cards;

1 1 .

1 1 2 1 2 20 1 20 200

1 1 3 1 3 30 1 30 300 1 300 3000

2 9 .

2 9 4 2 4 5 2 4 6 2 6 5000

2 6 7 2 7 8 2 6 400 2 6 4000

2 6 10 2 6 11 2 6 12

;

run;

data FormV1 / view=FormV1;

if 0 then set Forms;

declare hash C(dataset:'Forms(where=(Child is not null))', MultiData:'Y', Ordered:'Y');

C.definekey('FormID', 'Parent');

C.definedata('Parent', 'Child');

C.definedone();

declare hash P(dataset:'Forms(where=(Child is null))', Ordered:'Y');

P.definekey('FormID', 'Parent');

P.definedone();

declare hiter Pi('P');

declare hash F(Ordered:'Y');

F.definekey('FormID', 'Generation', 'Parent', 'Child');

F.definedone();

declare hiter Fi('F');

r=Pi.first();

do while(r=0); *FormID Loop;

Generation=1;

link search;

r1=Fi.first();

do while(r1=0);

Generation+1;

Parent=Child;

link search;

r1=Fi.next();

end;

F.clear();

r=Pi.next();

end;

drop r r1 rc;

stop;

search:

rc=C.find();

put _all_;

do while(rc=0);

output;

F.add();

rc=C.find_next();

end;

return;

run;

data FormV2 / view=FormV2;

do Cidx=1 by 1 until(last.Generation);

set FormV1;

by FormID Generation;

if first.FormID then P=Parent; else call missing(P);

output;

end;

run;

proc transpose

data=FormV2

out=FormT(drop=_NAME_)

prefix=G

delim=C;

by FormID;

copy P;

id Generation Cidx;

var Child;

run;

Re: Collapsing Data (multiple lines to one)

PGStats — Thu, 25 Dec 2014 03:47:17 GMT

I agree with this can be viewed as a network (or tree) problem. How exactly would this FormID collapse:

FormID Parent# Child#

1 1

1 1 2

1 2 20

1 2 30

1 2 40

1 20 200

1 40 500

Re: Collapsing Data (multiple lines to one)

drjohn61 — Thu, 25 Dec 2014 04:57:39 GMT

Thanks All,

FIrst--I have to say I am an absolute Newbie to SAS...so, please bear with me...

Second...not sure on SAS/OR... I will have to ask our techies...

To give more info on the issue:

This is pathogen sampling data and is in a database that I pull using SQL...

For each sample, there is an associated form with a unique ID. Each sample is assigned a 'ParentSample' Number. You always know which is the parent because the ChildSample# is null... (Actually in the DB, the ParentSample is Null and the ChildSample not--the ChildSample is then the ParentSample. I just swap this in the SQL used to pull the data).

One sample could be analyzed for multiple pathogens...e.g., Salmonella and Campylobacter...

Each Pathogen can have multiple isolates with its own serotype and other factors...

So you could have (This is how I would like the data):

Form# 1

ParentSample#1 -- Salmonella Positive / Campylobacter Positive

ChildSample#2 Salmonella Enteritidis => Grandchild#3 PFGE Pattern => GreatGranchild#4 Antimicrobial Resistance

ChildSample#5 Salmonella Heidelberg => Grandchild#6 PFGE Pattern => GreatGranchild#7 Antimicrobial Resistance

ChildSample#8 Campylobacter Jejuni => Grandchild#9 PFGE Pattern

I used consecutive numbers in the example--actually they are multidigit numbers--I just used the above to show that they should be unique within the FormID/ParentSample

The data is always split between the variables ParentSample and ChildSample with the added info (e.g. Pathogen, Serotype, Antimicrobial Resistance) in additional variables...

So, the above would be in the DB as such:

FormID# ParentSample# ChildSample# Info

1 1 - Salmonella + / Campylobacter +

1 1 2 Enteritidis

1 2 3 PFGE

1 3 4 Antimicrobial Resistance

1 1 5 Heidelberg

1 5 6 PFGE

1 6 7 Antimicrobial Resistance

1 1 8 Jejuni

1 8 9 PFGE

Normally, it will be sorted by FormID, ParentSample, ChildSample:

FormID# ParentSample# ChildSample# Info

1 1 - Salmonella + / Campylobacter +

1 1 2 Enteritidis

1 1 5 Heidelberg

1 1 8 Jejuni

1 2 3 PFGE

1 3 4 Antimicrobial Resistance

1 5 6 PFGE

1 6 7 Antimicrobial Resistance

1 8 9 PFGE

I should also mention that normally there is only one Grandchild, GreatGrandChild, etc per Child... So, there can be multiple Children per Parent--but usually everything from there is singular back to the Child.

So PGStats data above would collapse like this:

1 2 20 200

1 2 30

1 2 40 500

Re: Collapsing Data (multiple lines to one)

FriedEgg — Thu, 25 Dec 2014 05:27:48 GMT

This new criteria makes my previous posting only directional and not accurate to the problem at hand.

Re: Collapsing Data (multiple lines to one)

Reeza — Thu, 25 Dec 2014 06:44:42 GMT

You can run

proc setinit; run;

and check the log to see if you have SAS/OR. If you're in academia it's highly likely, but the solution to your problem goes beyond my skill set.

Re: Collapsing Data (multiple lines to one)

Ksharp — Thu, 25 Dec 2014 06:54:52 GMT

You could refer to this paper I wrote . I indeed love your question .:smileylaugh:


data Forms;
input FormID $ Parent $ Child $ @@;
cards;
1  1 .  
1  1 2   1  2 20 1 20 200
1  1 3   1  3 30 1 30 300   1 300 3000
2  9  .
2  9 4   2  4 5   2  4 6   2   6 5000
2  6 7   2  7 8   2  6 400 2   6 4000
2  6 10 2  6 11   2 6  12
;
run;


data temp(keep= path);
if _n_ eq 1 then do;
length path _path  $ 400 ;

if 0 then set Forms;
declare hash ha(hashexp:20,dataset:'Forms(where=(child is not missing))',multidata:'Y');
ha.definekey('FormID','Parent');
ha.definedata('Child');
ha.definedone();

declare hash pa(ordered:'Y');
declare hiter hi_path('pa');
pa.definekey('count');
pa.definedata('path');
pa.definedone();
end;

set Forms(where=(child is not missing));
count=1;
path=catx(' ',FormID,Parent,Child); 
pa.add();
do while(hi_path.next()=0);
_path=path;  
FormID=scan(path,1,' ');
Parent=scan(path,-1,' '); 
rc=ha.find(); if rc ne 0 then output; /*the path is deepest, we output it.*/
 do while(rc=0);
  if not find(path,strip(Child)) then do;
                                      count+1;
                                      path=catx(' ',path,Child);
                                      pa.add(); 
                                      path=_path;
                                        end;
  rc=ha.find_next();
 end;
end;
pa.clear();
run;
data want(keep=want);
 set temp;
 length want $ 400;
 do i=1 to countw(path);
   if i=1 then want=catx(' ',want,cats('FID-',scan(path,i,' ')));
    else if i=2 then want=catx(' ',want,cats('P-',scan(path,i,' ')));
      else  if i=3 then want=catx(' ',want,cats('C-',scan(path,i,' ')));
       else  want=catx(' ',want,cats('G',i-3,'C-',scan(path,i,' ')));
 end;
run;

Xia Keshan

Re: Collapsing Data (multiple lines to one)

Ksharp — Thu, 25 Dec 2014 08:44:59 GMT

Make it Better !

data Forms;

input FormID $ Parent $ Child $ @@;

cards;

1 1 .

1 1 2 1 2 20 1 20 200

1 1 3 1 3 30 1 30 300 1 300 3000

2 9 .

2 9 4 2 4 5 2 4 6 2 6 5000

2 6 7 2 7 8 2 6 400 2 6 4000

2 6 10 2 6 11 2 6 12

;

run;

data check;

set forms ;

P=Child;C=Parent;

drop Parent Child;

run;

data temp(keep= path);

if _n_ eq 1 then do;

length path _path $ 400 ;

if 0 then set check;

declare hash ch(hashexp:20,dataset:'check(where=(P is not missing))');

ch.definekey('FormID','P');

ch.definedone();

if 0 then set Forms;

declare hash ha(hashexp:20,dataset:'Forms(where=(child is not missing))',multidata:'Y');

ha.definekey('FormID','Parent');

ha.definedata('Child');

ha.definedone();

declare hash pa(ordered:'Y');

declare hiter hi_path('pa');

pa.definekey('count');

pa.definedata('path');

pa.definedone();

end;

set Forms(where=(child is not missing));

count=1;

path=catx(' ',FormID,Parent,Child);

pa.add();

do while(hi_path.next()=0);

_path=path;

FormID=scan(path,1,' ');

P=scan(path,2,' ');

Parent=scan(path,-1,' ');

rc=ha.find();

if (rc ne 0 or (rc eq 0 and find(path,strip(Child)))) and ch.check() ne 0 then output;

do while(rc=0);

if not find(path,strip(Child)) then do;

count+1;

path=catx(' ',path,Child);

pa.add();

path=_path;

end;

rc=ha.find_next();

end;

pa.clear();

run;

data want(keep=want);

set temp;

length want $ 400;

do i=1 to countw(path);

if i=1 then want=catx(' ',want,cats('FID-',scan(path,i,' ')));

else if i=2 then want=catx(' ',want,cats('P-',scan(path,i,' ')));

else if i=3 then want=catx(' ',want,cats('C-',scan(path,i,' ')));

else want=catx(' ',want,cats('G',i-3,'C-',scan(path,i,' ')));

end;

run;

Xia Keshan

Re: Collapsing Data (multiple lines to one)

drjohn61 — Thu, 25 Dec 2014 19:00:25 GMT

I appreciate the help. I am sorry I was not explicit enough. Being new to this whole SAS thing and trying to solve this has been driving me crazy... I am pretty sure I could solve it in Access with VBA...but I don't want to have to run the data through two systems...

Re: Collapsing Data (multiple lines to one)

drjohn61 — Thu, 25 Dec 2014 19:01:38 GMT

Thanks...this is a bit above my level by just looking at it. I am going to have to run it and see what I get. Playing is how I learn things...

Re: Collapsing Data (multiple lines to one)

drjohn61 — Thu, 25 Dec 2014 19:06:48 GMT

It seems I have:

OR OPT

OR PRS

OR IVS

OR LVS

Of course, I don't really know what any of that means...

Re: Collapsing Data (multiple lines to one)

drjohn61 — Thu, 25 Dec 2014 22:21:08 GMT

Xia,

This is AWESOME! The only thing that I need different is the last step... I probably confused things a bit above...

Instead of the output:

Want

FID-1 P-1 C-2 G1-3 G2-4 G3-5

I need the outout:

FID P C G1 G2 G3

1 1 2 3 4 5

In other words, I need the prefixes you concatenated onto the numbers to be the variable names.

Re: Collapsing Data (multiple lines to one)

Patrick — Thu, 25 Dec 2014 23:17:24 GMT

Tampering with 's code below changes in bold should give you what you're after.

data temp(keep= path);

if _n_ eq 1 then

do;

length path _path $ 400;

if 0 then set check;

declare hash ch(hashexp:20,dataset:'check(where=(P is not missing))');

ch.definekey('FormID','P');

ch.definedone();

if 0 then set Forms;

declare hash ha(hashexp:20,dataset:'Forms(where=(child is not missing))',multidata:'Y');

ha.definekey('FormID','Parent');

ha.definedata('Child');

ha.definedone();

declare hash pa(ordered:'Y');

declare hiter hi_path('pa');

pa.definekey('count');

pa.definedata('path');

pa.definedone();

end;

set Forms(where=(child is not missing)) end=last;

count=1;

path=catx(' ',FormID,Parent,Child);

pa.add();

do while(hi_path.next()=0);

_path=path;

FormID=scan(path,1,' ');

P=scan(path,2,' ');

Parent=scan(path,-1,' ');

rc=ha.find();

if (rc ne 0 or (rc eq 0 and find(path,strip(Child)))) and ch.check() ne 0 then

output;

do while(rc=0);

if not find(path,strip(Child)) then

do;

count+1;

path=catx(' ',path,Child);

pa.add();

path=_path;

end;

rc=ha.find_next();

end;

pa.clear();

retain max_levels;

max_levels=max(countw(path),max_levels);

if last then call symputx('max_levels',max(4,max_levels));

run;

data want (keep=FID P C G:);

set temp;

array el {*} 8. FID P C G1 - G%eval(&max_levels-3);

do i=1 to countw(path);

el=scan(path,i,' ');

end;

run;

Re: Collapsing Data (multiple lines to one)

Ksharp — Fri, 26 Dec 2014 06:06:30 GMT

Here is my version.

Hi. Actually Patrick has already done it for me .Thanks Patrick. Happy Holiday!

data Forms;

input FormID $ Parent $ Child $ @@;

cards;

1 1 .

1 1 2 1 2 20 1 20 200

1 1 3 1 3 30 1 30 300 1 300 3000

2 9 .

2 9 4 2 4 5 2 4 6 2 6 5000

2 6 7 2 7 8 2 6 400 2 6 4000

2 6 10 2 6 11 2 6 12

;

run;

data check;

set forms ;

P=Child;C=Parent;

drop Parent Child;

run;

data temp(keep= path);

if _n_ eq 1 then do;

length path _path $ 400 ;

if 0 then set check;

declare hash ch(hashexp:20,dataset:'check(where=(P is not missing))');

ch.definekey('FormID','P');

ch.definedone();

if 0 then set Forms;

declare hash ha(hashexp:20,dataset:'Forms(where=(child is not missing))',multidata:'Y');

ha.definekey('FormID','Parent');

ha.definedata('Child');

ha.definedone();

declare hash pa(ordered:'Y');

declare hiter hi_path('pa');

pa.definekey('count');

pa.definedata('path');

pa.definedone();

end;

set Forms(where=(child is not missing));

count=1;

path=catx(' ',FormID,Parent,Child);

pa.add();

do while(hi_path.next()=0);

_path=path;

FormID=scan(path,1,' ');

P=scan(path,2,' ');

Parent=scan(path,-1,' ');

rc=ha.find();

if (rc ne 0 or (rc eq 0 and find(path,strip(Child)))) and ch.check() ne 0 then output;

do while(rc=0);

if not find(path,strip(Child)) then do;

count+1;

path=catx(' ',path,Child);

pa.add();

path=_path;

end;

rc=ha.find_next();

end;

pa.clear();

run;

proc sql noprint;

select max(n)-3 into : n separated by ' '

from (select countw(path) as n from temp);

quit;

data want;

set temp;

array x{*} $ 40 FID P C G1-G&n;

do i=1 to dim(x);

x{i}=scan(path,i,' ');

end;

drop path i;

run;

Xia Keshan

Re: Collapsing Data (multiple lines to one)

drjohn61 — Fri, 26 Dec 2014 15:47:35 GMT

Thanks All,

I REALLY appreciate the help. This will streamline my code and the time needed to run it immensely!

I would have liked to award TWO correct answers, since both Patrick and Xia's are correct....but it won't let me. Xia still gets 20 points though!

Thanks again,

John L.

Re: Collapsing Data (multiple lines to one)

billfish — Mon, 29 Dec 2014 17:07:09 GMT

My suggested solution is not as sophisticated as the ones already presented. Looking at the input and output, I notice that
1- A given FormId correspond to the same Parent
2- The child at of the line for each line does not show up in the parent column.

The following solution assumes that there is no lineage with more than 9999 generations.

data want(keep=FormID P1 G1-G9999);

if 0 then set forms; *just want FormID to be first variable in output;

array PP(10000) ; * parent;

array CA(10000) ; * child ;

array CB(10000) ; * end_of_line children;

array LA(10000) P1 G1-G9999; * lineage sequence;

r=0;

zP=.;

call missing(of PP(*));

call missing(of CA(*));

call missing(of CB(*));

* load data for given FormID in matrices PP CA;

do until (last.FormID);

set forms end=zEnd;

by FormID;

r+1;

PP(r)=Parent;

CA(r)=Child;

if Child=. then zP=Parent;

end;

* find the end_of_line Children ;

s=0;

do i= 1 to r;

if not(CA(i)=.) and not(CA(i) in PP) then do;

s+1;

CB(s)=CA(i);

end;

* for each end_of_line child, find the lineage;

do i = 1 to s;

t=1;

LA(t)=CB(i);

zDone=0;

do until (zDone);

do j = 1 to r;

if LA(t)=CA(j) then do;

t+1;

LA(t)=PP(j);

if LA(t)=zP then zDone=1;

leave;

end;

t_max=max(t_max,t);

do j = 1 to int(t/2); *reverse lineage sequence;

a1 = LA(j);

LA(j)=LA(t+1-j);;

LA(t+1-j)= a1;

end;

output;

call missing(of LA(*)); * clear for next lineage;

end;

* delete all G# which is null for all lineages;

if zEnd then do;

call execute('data want; set want(keep=FormID P1 G1-'||compress('G'||put(t_max-1,5.))||');run;');

end;

run;

Re: Collapsing Data (multiple lines to one)

billfish — Wed, 07 Jan 2015 20:28:17 GMT

I propose a different SAS Hash-object solution.

The first DATA STEP creates a simulated geneological (Parent,Child) table creating some lineages with more than 90 generations.
The second DATA STEP is an alternative (proposed) solution using SAS Hash objects and arrays.

Using the array, non-Hash-object solution which I have presented previously in this forum, 4674 lineages are presented. It took 7 minutes.

Using the hash methods presented previously here, using the criterion:
hashexp:20
produces the following SAS error message:
ERROR: Hash object added 8912880 items when memory failure occurred.

Using the hash methods presented previously here, using the criterion:
hashexp:40
produces in the SAS LOG:
NOTE: There were 4305 observations read from the data set WORK.TEMP.
The hash methods took 10 seconds.

Using the hash methods presented previously here, using the criterion:
no hashexp used
produces in the SAS LOG:
NOTE: The data set WORK.TEMP has 4305 observations and 1 variables.
The hash methods took 10 seconds and gave identical results as with the (hashexp:40) criterion.

So the array method produced 369 (=4674-4305) more lineages than the hash methods presented in this forum.

in_ary = 1 when the lineage is present in the array (non-hash) method.
in_hsh = 1 when the lineage is present in the hash method(s).

Doing a full join on the 2 set of lineages, the results are:

in_ary in_hsh c_tot
0       1       51
1       0      420
1       1     4254

Let us look briefly some of the 51 lineages present in the hash results and not the array method.

Let us look at the hash lineages where the end-of-line (or latest) child is 495, 806, 1032. According to the hash results:
The lastest child 495 occurs at the 66th generation.
The lastest child 806 occurs at the 89th generation.
The lastest child 1032 occurs at the 83rd generation.

Looking at the input table Forms for either Parent in (495,806,1032), one finds:

FormID Parent Child

101      1032     417
101      1032     8796
101      1032     4866
101      1032     3620
101      495      474
101      495      6750
101      806      791

Thus the supposed terminal (end-of-line) children produce other children.

Given the SAS system I was using (at a Fortune 500 company), the hash methods previously presented here did not complete the task.

Hash methods are fast, so I wrote a hash-object solution. It produces 4674 lineages. It took 4 secs. Going from 7 minutes to 4 secs is progress.

/*********************/
/**** DATA STEP 1 ****/
/*********************/
data t_a(keep=zParent zChild rnd);
   array elemA(10000) (1:10000);
   array genA(500); ** start index of a generation **;
   array genB(500); ** end   index of a generation **;
   ** scramble the elemA matrix elements;
   do i = 1 to 7000;
      i1 = ceil(10000*ranuni(3));
      i2 = ceil(10000*ranuni(5));
      x = elemA(i1);
      elemA(i1) = elemA(i2);
      elemA(i2) = x;
   end;
   r=0; ** count number of generations;
   c=0; ** count nuber of children;
   do while (c<=10000);
      r+1;
      if (r=1) then do;
         c+1;
         genA(1)=1;
         genB(1)=1;
      end;
      else do;
         g= 10+ceil(200*ranuni(27));
         do j=1 to g;
            c+1;
            if (c>10000) then leave;
            else do;
               if j=1 then genA(r)=c;
               genB(r)=c;
            end;
         end;
      end;
   end;
   ** for given child generation: connect child to parent;
   do i1 = r to 2 by -1;
      do i2 = genA(i1) to genB(i1);
         g_sum= genB(i1-1)-genA(i1-1)+1;
         x1= genA(i1-1) -1 +ceil(g_sum*ranuni(3));
         zParent= elemA(x1);
         zChild = elemA(i2);
         rnd=ranuni(19);
         output;
      end;
   end;
run;

proc sort data=t_a; by rnd; run; *** just to make things interesting;

/** transform table t_a to be compatible with previous Hash solutions **/
data Forms(keep=FormID Parent Child);
   length FormID Parent Child $8.;
   set t_a;
   FormID = '101';
   Parent = strip(put(zParent,8.));
   Child = strip(put(zChild,8.));
run;

/*********************/
/**** DATA STEP 2 ****/
/*********************/
data t_want(keep=t_gen P1 G1-G9999);
   length c_end 8.;
   t_max=0;
   array GG(10000) P1 G1-G9999;

   if _N_=1 then do;
      if 0 then set t_a;
      declare hash ha(dataset:'t_a',multidata:'N' );
      ha.definekey('zChild');
      ha.definedata('zChild','zParent');
      ha.definedone();

      declare hash hb(dataset:'t_a',multidata:'N' );
      hb.definekey('zParent');
      hb.definedata('zChild','zParent');
      hb.definedone();

      declare hash hd(multidata:'N');
      hd.definekey('c_end');
      declare hiter dIter('hd');
      hd.definedone();
   end;

   do until (zDone); ** make list of end-of-line children;
      set t_a(rename=(zchild=zc zparent=zp)) nobs=n_last end=zDone;
      zParent=zC;
      if hb.find() then do;
         c_end=zParent;
         if hd.check() then do; hd.add(); end;
      end;
   end;

   rc = dIter.first();
   do while (rc = 0);
      aDone=0;
      t_gen=0;
      do until (aDone);
              if (t_gen=0)      then do; t_gen+1; GG(t_gen)= c_end;   zChild=c_end; end;
         else if not(ha.find()) then do; t_gen+1; GG(t_gen)= zParent; zChild=zParent; end;
         else if     ha.find() then do; aDone=1; end;
      end;
      do i=1 to int(t_gen/2); ** reverse lineage sequence;
         x1= GG(i);
         GG(i)= GG(t_gen+1-i);
         GG(t_gen+1-i)=x1;
      end;
      output;
      call missing(of GG(*));
      t_max=max(t_max,t_gen);
      rc = dIter.next();
   end;

call execute('data t_want; set t_want(keep=t_gen P1 G1-'||compress('G'||put(t_max-1,6.))||');run;');
run;

Re: Collapsing Data (multiple lines to one)

Ksharp — Thu, 08 Jan 2015 02:28:08 GMT

I can optimize it as well , if you want.

And apparently your code didn't consider the LOOP situation .

if data like 1 1 2 1 2 20 1 20 200 1 200 201 1 201 20

You will get the wrong result .