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
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.
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.