topic Re: Where is ROW in proc sql? in SAS Programming

Where is ROW in proc sql?

buckeye — Thu, 26 May 2011 13:26:01 GMT

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;

Re: Where is ROW in proc sql?

SPR — Thu, 26 May 2011 15:06:19 GMT

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

Re: Where is ROW in proc sql?

buckeye — Thu, 26 May 2011 15:51:54 GMT

That is exactly what I need. Thanks a lot.

Re: Where is ROW in proc sql?

Cynthia_sas — Thu, 26 May 2011 19:33:31 GMT

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

Re: Where is ROW in proc sql?

darrylovia — Thu, 26 May 2011 20:01:38 GMT

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!

Re: Where is ROW in proc sql?

SPR — Thu, 26 May 2011 20:20:17 GMT

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

Re: Where is ROW in proc sql?

Cynthia_sas — Thu, 26 May 2011 22:00:02 GMT

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