- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 05-26-2011 09:26 AM
(1427 views)
Hi all,
With Number specified in proc sql as an option shown below, but I couldn't get it in the subsequent data file mytable.
Is the ROW just for the printing purpose and we can not use it as a field or column? Thanks in advance.
data tbl;
input first $1. second third fourth ;
datalines
;
a 21 22 23
s 31 32 33
d 41 42 43
f 51 52 53
;
run
;
proc sql number ;
create table mytbl as
select * from tbl;
/* select statements */
select * from mytbl;
select 'r' || row as myrownumber, * from mytbl;
quit;
With Number specified in proc sql as an option shown below, but I couldn't get it in the subsequent data file mytable.
Is the ROW just for the printing purpose and we can not use it as a field or column? Thanks in advance.
data tbl;
input first $1. second third fourth ;
datalines
;
a 21 22 23
s 31 32 33
d 41 42 43
f 51 52 53
;
run
;
proc sql number ;
create table mytbl as
select * from tbl;
/* select statements */
select * from mytbl;
select 'r' || row as myrownumber, * from mytbl;
quit;
6 REPLIES 6
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hello Buckeye,
If you would like to number your rows like r1, r2, r3, etc. then it could be done like this:
[pre]
proc SQL;
create table mytbl as
select CATS("r",put(MONOTONIC(),6.)) as myrownumber, * from tbl;
quit;
proc print data=mytbl noobs;
run;
[/pre]
Sincerely,
SPR
If you would like to number your rows like r1, r2, r3, etc. then it could be done like this:
[pre]
proc SQL;
create table mytbl as
select CATS("r",put(MONOTONIC(),6.)) as myrownumber, * from tbl;
quit;
proc print data=mytbl noobs;
run;
[/pre]
Sincerely,
SPR
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
That is exactly what I need. Thanks a lot.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi:
It is up to you whether to use MONOTONIC() or not. However, you should be aware of this Tech Support note:
http://support.sas.com/kb/15/138.html
cynthia
It is up to you whether to use MONOTONIC() or not. However, you should be aware of this Tech Support note:
http://support.sas.com/kb/15/138.html
cynthia
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I've used the MONOTINC function with no ill effects for one table processing. I believe when joins occur the MONOTONIC function may fail. Sounds like a fun experiment!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hello Cynthia,
There is a solution without MONOTONIC():
[pre]
data tbl;
input first $1. second third fourth ;
row+1;
datalines;
a 21 22 23
s 31 32 33
d 41 42 43
f 51 52 53
run;
proc SQL;
create table mytbl(drop=row) as
select CATS("r",put(row,6.)) as myrownumber, * from tbl;
quit;
[/pre]
Sincerely,
SPR
There is a solution without MONOTONIC():
[pre]
data tbl;
input first $1. second third fourth ;
row+1;
datalines;
a 21 22 23
s 31 32 33
d 41 42 43
f 51 52 53
run;
proc SQL;
create table mytbl(drop=row) as
select CATS("r",put(row,6.)) as myrownumber, * from tbl;
quit;
[/pre]
Sincerely,
SPR
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi:
Yes, that is probably what I would do if I needed a row-type identifier. As long as it is understood that when adding or deleting observations from the table, some action would need to be taken to redo the numbers and/or if sorting in some different order, then the numbers might appear out of their original order.
cynthia
Yes, that is probably what I would do if I needed a row-type identifier. As long as it is understood that when adding or deleting observations from the table, some action would need to be taken to redo the numbers and/or if sorting in some different order, then the numbers might appear out of their original order.
cynthia