Help using Base SAS procedures

How can I get this in proc sql?

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

How can I get this in proc sql?

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!


Accepted Solutions
Solution
‎02-12-2012 01:38 PM
Valued Guide
Posts: 765

Re: How can I get this in proc sql?

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

View solution in original post


All Replies
PROC Star
Posts: 7,363

How can I get this in proc sql?

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

  monotonic() as num

Contributor
Posts: 60

How can I get this in proc sql?

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

THX!

PROC Star
Posts: 7,363

Re: How can I get this in proc sql?

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.

Contributor
Posts: 60

How can I get this in proc sql?

Oh, really, I will think about it.

THX again!

Solution
‎02-12-2012 01:38 PM
Valued Guide
Posts: 765

Re: How can I get this in proc sql?

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

Contributor
Posts: 60

How can I get this in proc sql?

Oh, it's interesting.

THX!

Super User
Posts: 9,681

Re: How can I get this in proc sql?

Mike.

Long time no see.

Thank you to mention me . Smiley Happy

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

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

Ksharp

PROC Star
Posts: 7,363

Re: How can I get this in proc sql?

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 865 views
  • 7 likes
  • 4 in conversation