Solved
Contributor
Posts: 71

# Generate sequence

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

Accepted Solutions
Solution
‎02-28-2014 02:20 AM
Frequent Contributor
Posts: 106

## Re: Generate sequence

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;

All Replies
Solution
‎02-28-2014 02:20 AM
Frequent Contributor
Posts: 106

## Re: Generate sequence

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;

Super User
Posts: 9,441

## Re: Generate sequence

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;

Frequent Contributor
Posts: 106

## Re: Generate sequence

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;

Valued Guide
Posts: 2,191

## Re: Generate sequence

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

Super User
Posts: 9,441

## Re: Generate sequence

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

Contributor
Posts: 71

## Re: Generate sequence

thanks for the replys I solved issue using retain

🔒 This topic is solved and locked.