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
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;
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;
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;
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;
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
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
thanks for the replys I solved issue using retain
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.