DATA Step, Macro, Functions and more

Where is ROW in proc sql?

Reply
Contributor
Posts: 24

Where is ROW in proc sql?

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;
Super Contributor
Super Contributor
Posts: 365

Re: Where is ROW in proc sql?

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
Contributor
Posts: 24

Re: Where is ROW in proc sql?

That is exactly what I need. Thanks a lot.
SAS Super FREQ
Posts: 8,866

Re: Where is ROW in proc sql?

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
Frequent Contributor
Posts: 139

Re: Where is ROW in proc sql?

Posted in reply to Cynthia_sas
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!
Super Contributor
Super Contributor
Posts: 365

Re: Where is ROW in proc sql?

Posted in reply to Cynthia_sas
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
SAS Super FREQ
Posts: 8,866

Re: Where is ROW in proc sql?

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
Ask a Question
Discussion stats
  • 6 replies
  • 193 views
  • 0 likes
  • 4 in conversation