topic Re: Generate sequence in SAS Programming

Generate sequence

pallis — Fri, 28 Feb 2014 04:20:47 GMT

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

Re: Generate sequence

pradeepalankar — Fri, 28 Feb 2014 07:20:01 GMT

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;

Re: Generate sequence

RW9 — Fri, 28 Feb 2014 09:28:39 GMT

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;

Re: Generate sequence

pradeepalankar — Fri, 28 Feb 2014 09:47:06 GMT

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;

Re: Generate sequence

Peter_C — Fri, 28 Feb 2014 10:41:14 GMT

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

Re: Generate sequence

RW9 — Fri, 28 Feb 2014 10:47:59 GMT

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

Re: Generate sequence

pallis — Fri, 14 Mar 2014 02:38:32 GMT

thanks for the replys I solved issue using retain