How can I get this in proc sql?

[rpg163]

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!

[art297]

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

  monotonic() as num

[rpg163]

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

THX!

[art297]

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.

[rpg163]

Oh, really, I will think about it.

THX again!

[MikeZdeb · Accepted solution]

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

[rpg163]

Oh, it's interesting.

THX!

[Ksharp]

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

[art297]

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