how can i translate subquery(SQL) to Datastep
in [SAS]
|
Prev: Data input format help
Next: Two research positions (PhD) in translational developmental neuroscience (with a focus on eating disorders OR schizophrenia)
From: Rex on 10 Aug 2010 22:45 HI all.. i'm kinda newbie to use data step and really i'm not used to using it.. so i always use SQL but i think it's not good at all.. I have simple SQL below create table have as select * from [data_set_name] where trln_nm in (select dmdp_c from [data_set_name]); quit; how can i translate above one to simple one data step?? i'm gonna thank you for it in advance.... REX(Yu, Hyo jong)
From: Ya on 11 Aug 2010 10:50 On Aug 10, 7:45 pm, Rex <opasdf0...(a)hanmail.net> wrote: > HI all.. > i'm kinda newbie to use data step and really i'm not used to using > it.. > so i always use SQL but i think it's not good at all.. > > I have simple SQL below > > create table have as > select * > from [data_set_name] > where trln_nm in (select dmdp_c > from [data_set_name]); > quit; > > how can i translate above one to simple one data step?? > i'm gonna thank you for it in advance.... > > REX(Yu, Hyo jong) Something like this should work: proc sort data=xx; by trln_nm; run; proc sort data=yy out=yy (keep=dmdp_c rename=(dmdp_c=trln_nm)) nodupkey; by dmdp_c; run; data have; merge xx yy (in=b_); by trln_nm; if b_; run; HTH Ya
From: Reeza on 11 Aug 2010 11:49 On Aug 10, 7:45 pm, Rex <opasdf0...(a)hanmail.net> wrote: > HI all.. > i'm kinda newbie to use data step and really i'm not used to using > it.. > so i always use SQL but i think it's not good at all.. > > I have simple SQL below > > create table have as > select * > from [data_set_name] > where trln_nm in (select dmdp_c > from [data_set_name]); > quit; > > how can i translate above one to simple one data step?? > i'm gonna thank you for it in advance.... > > REX(Yu, Hyo jong) In the case you have there, SQL is more efficient that a datastep and easier to follow, to me at least. Reeza
From: Rex on 11 Aug 2010 21:33
On Aug 11, 11:50 pm, Ya <huang8...(a)gmail.com> wrote: > On Aug 10, 7:45 pm, Rex <opasdf0...(a)hanmail.net> wrote: > > > > > > > HI all.. > > i'm kinda newbie to use data step and really i'm not used to using > > it.. > > so i always use SQL but i think it's not good at all.. > > > I have simple SQL below > > > create table have as > > select * > > from [data_set_name] > > where trln_nm in (select dmdp_c > > from [data_set_name]); > > quit; > > > how can i translate above one to simple one data step?? > > i'm gonna thank you for it in advance.... > > > REX(Yu, Hyo jong) > > Something like this should work: > > proc sort data=xx; > by trln_nm; > run; > > proc sort data=yy out=yy (keep=dmdp_c rename=(dmdp_c=trln_nm)) > nodupkey; > by dmdp_c; > run; > > data have; > merge xx yy (in=b_); > by trln_nm; > if b_; > run; > > HTH > > Ya- Hide quoted text - > > - Show quoted text - thx ya!! but, actually i have over over 50000000rows in the data set beside too many variables.. so i guess proc sort step will be too late.. BTW really good to know your solution for translating from subquery to data step.. |