BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
rpg163
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
MikeZdeb
Rhodochrosite | Level 12

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

8 REPLIES 8
art297
Opal | Level 21

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

  monotonic() as num

rpg163
Calcite | Level 5

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

THX!

art297
Opal | Level 21

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
Calcite | Level 5

Oh, really, I will think about it.

THX again!

MikeZdeb
Rhodochrosite | Level 12

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
Calcite | Level 5

Oh, it's interesting.

THX!

Ksharp
Super User

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

art297
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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