topic How can I get this in proc sql? in SAS Procedures

How can I get this in proc sql?

rpg163 — Sun, 12 Feb 2012 10:51:48 GMT

Hi,

I want get a variable (num) in sql procedure

this is sql code:

proc sql;

create table rm as

select distinct year(date) as year,week(date) as week,sum(_COL7) as rm

from stk.MarketIndex as A

group by year(date),week(date);

quit;

and then

data rm2;

set rm;

num=_N_;

run;

can I do it in sql?

THX!

How can I get this in proc sql?

art297 — Sun, 12 Feb 2012 14:08:20 GMT

Its an undocumented function, thus you may or may not want to use it, but you could add:

monotonic() as num

How can I get this in proc sql?

rpg163 — Sun, 12 Feb 2012 14:24:54 GMT

Oh, It's interesting. I am trying it.

THX!

Re: How can I get this in proc sql?

art297 — Sun, 12 Feb 2012 14:28:41 GMT

While it conflicts with the concept that sql isn't record order dependent, I have yet to see a case where one obtains a result different then what they would get from using a datastep.

How can I get this in proc sql?

rpg163 — Sun, 12 Feb 2012 14:31:44 GMT

Oh, really, I will think about it.

THX again!

Re: How can I get this in proc sql?

MikeZdeb — Sun, 12 Feb 2012 18:38:39 GMT

hi ... part of the discussion in http://communities.sas.com/message/113261 included info from Ksharp on using ODS to get a new variable named ROW that is a counter similar to _N_ from a data step ...

it's more SAS code than monotonic(), but it's not 'undocumented' ...

ods listing close ;

ods output sql_results=class (rename=(row=num));

proc sql number;

select distinct sex, age from sashelp.class

order sex, age desc;

quit;

ods listing;

proc print data=class noobs;

run;

num Sex Age

1 F 15

2 F 14

3 F 13

4 F 12

5 F 11

6 M 16

7 M 15

8 M 14

9 M 13

10 M 12

11 M 11

How can I get this in proc sql?

rpg163 — Sun, 12 Feb 2012 20:11:40 GMT

Oh, it's interesting.

THX!

Re: How can I get this in proc sql?

Ksharp — Mon, 13 Feb 2012 03:42:00 GMT

Mike.

Long time no see.

Thank you to mention me .

Yes. Function monotonic() is not respected in proc sql.

http://support.sas.com/kb/15/138.html

Ksharp

Re: How can I get this in proc sql?

art297 — Mon, 13 Feb 2012 03:48:45 GMT

Ksharp, Not documented .. agreed! Not respected? Take a look at: http://communities.sas.com/thread/13039