Generate sequence

[pallis]

Hi All,

  I have two tables contains . I want to generate sequence for each of these tables.If table-1 has 1- 20 sequence and table-2 must start with 21- x numbers(Records gets increases in this table based on count i need to increase the numbers.). How to generate. Can you help is generating sequence for table-2.

Regards,

venky

[pradeepalankar · Accepted solution]

hi it can be done by multiple ways couple are below:

/*method 1*/

data test1;

do sequence=1 to 20;

output;

end;

run;

data test2;

if _N_=0 then set test1 nobs=nobs;

sequence=nobs+1;

run;

/*method2*/

data test1;

do sequence=1 to 20;

output;

end;

run;

proc sql;

select count(*) into  :count from test1;

quit;

data test2;

do sequence=&count+1 to 100;output;end;

run;

[RW9]

Hi,

One other way is to use in and output with _n_:

/* This is the test data */

data first_dataset (drop=i);
  attrib mytext format=$20.;
  do i=1 to 46;
    mytext="AAANNDD";
    output;
  end;
run;
data second_dataset (drop=i);
  attrib mytext format=$20.;
  do i=1 to 30;
    mytext="GGHHYYSS";
    output;
  end;
run;

/* This is where the action happens */

data first_updated second_updated;
  set first_dataset (in=a) second_dataset (in=b);
  attrib id format=8.;
  id=_n_;              
  if a then output first_updated;
  else output second_updated;
run;

[pradeepalankar]

Update : getting rid of proc sql:

data test1;

do sequence=1 to 20;

output;

end;

call symputx('last_sequence',sequence);

run;

data test2;

do sequence=&last_sequence to 100;output;end;

run;

[Peter_C]

I do not understand the original question

Venky wrote

I have two tables 

In the solutions offered so far I see no reference to these tables.

Adding a variable to data normally involves writing a new table, but the question was

  I want to generate sequence for each of these tables.

Already SAS has a "sequence" that might be referred as "radix" for all base SAS datasets.

it is what  the POINT= option of a SET statement refers.

So, what is the purpose of the "sequence"?

Should the original tables be sorted before the "sequence" is added?

Is that reference to "1-20" dynamic based on 20 rows in the first table, or a 20-lag, or is it just an example to clarify how "sequence" is derived for/in the second table?

I think these "sequence" columns could (possibly should) be derived only in a view as the resultant data with sequences is used. The base SAS language supports this in two ways.

1  PROC SQL allows you to select a column filled with MONOTONIC() which is like a sequence. It is not documented but works (as long as all data is selected in a single process and not split across different threads - as we get a monotinic() sequence for each thread) e.g.

select d.*, ( 100+ monotonic() ) as sequence  from your.data D

2 DATA STEP : allows an accumulator to be defined with a statement which looks like an assignment with no equals (=).   E.g.

  Sequence+1  ;

Both of these ways can be embedded within data VIEWs. A view stores the process until it is invoked in a PROC SQL FROM clause or a DATA STEP SET statement (or MERGE etc.)

An example DATA STEP

data view2seq/VIEW= view2seq ;

Retain sequence &initial_number ;

Set your.dataset2;

sequence + 1 ;

Run;

Beware that "&initial_number" would be fixed when the view is compiled - before it is invoked. So that macro variable must be known at compile stage.

.

Regards,

peterC

[RW9]

Hi Peter.C,

In my post above I have the two datasets first_dataset and second_dataset.  In the third datastep - which is the example - I set the two together and using the SAS autogenerated  _n_ assign a consecutive identifier, then split the output back out again to first_updated, second_updated.  Hence ending up with two datasets, one with id's 1-x (depending on obs), the second has id's x+1 - y (y is number of obs + x+ 1).

Thanks

Rob

[pallis]

thanks for the replys I solved issue using retain