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!
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
Its an undocumented function, thus you may or may not want to use it, but you could add:
monotonic() as num
Oh, It's interesting. I am trying it.
THX!
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.
Oh, really, I will think about it.
THX again!
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
Oh, it's interesting.
THX!
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
Ksharp, Not documented .. agreed! Not respected? Take a look at: http://communities.sas.com/thread/13039
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.