What is the DATA step equivalent of this SQL query?
-1
Hi: Anyone who is good at SAS please turn this SQL into a DATA step:
create table tableD nologging as
select
a.acct,
b.app_dt,
case when a.acct in (select acct from tableC) then 1 else 0 end prom_ind
from tableA a
inner join tableB b
on a.application_no = b.application_no
where to_char(b.app_dt,'yyyymmdd') between '20150101' and '20150630' ;
quit;
sas
edited Nov 26 '18 at 13:18
Richard
9,72721329
asked Nov 26 '18 at 4:28
LucyLucy
916
add a comment |
-1
Hi: Anyone who is good at SAS please turn this SQL into a DATA step:
create table tableD nologging as
select
a.acct,
b.app_dt,
case when a.acct in (select acct from tableC) then 1 else 0 end prom_ind
from tableA a
inner join tableB b
on a.application_no = b.application_no
where to_char(b.app_dt,'yyyymmdd') between '20150101' and '20150630' ;
quit;
sas
edited Nov 26 '18 at 13:18
Richard
9,72721329
asked Nov 26 '18 at 4:28
LucyLucy
916
1
To get help please explain in words what the SQL is doing. Also provide sample input datasets and what the result dataset should be for that sample inputs.
– Tom
Nov 26 '18 at 5:43
you can use the same thing in proc sql except last where statment .you can chage it to app_dt between '01Jan2015'D and '30Jun2015'D
– Kiran
Nov 26 '18 at 11:44
1
What have you tried ? Do you know how toMERGEtwo data sets ? Do you know the data set optionin=?
– Richard
Nov 26 '18 at 13:20
add a comment |
-1
-1
-1
Hi: Anyone who is good at SAS please turn this SQL into a DATA step:
create table tableD nologging as
select
a.acct,
b.app_dt,
case when a.acct in (select acct from tableC) then 1 else 0 end prom_ind
from tableA a
inner join tableB b
on a.application_no = b.application_no
where to_char(b.app_dt,'yyyymmdd') between '20150101' and '20150630' ;
quit;
sas
edited Nov 26 '18 at 13:18
Richard
9,72721329
asked Nov 26 '18 at 4:28
LucyLucy
916
Hi: Anyone who is good at SAS please turn this SQL into a DATA step:
create table tableD nologging as
select
a.acct,
b.app_dt,
case when a.acct in (select acct from tableC) then 1 else 0 end prom_ind
from tableA a
inner join tableB b
on a.application_no = b.application_no
where to_char(b.app_dt,'yyyymmdd') between '20150101' and '20150630' ;
quit;
sas
sas
edited Nov 26 '18 at 13:18
Richard
9,72721329
asked Nov 26 '18 at 4:28
LucyLucy
916
edited Nov 26 '18 at 13:18
Richard
9,72721329
asked Nov 26 '18 at 4:28
LucyLucy
916
edited Nov 26 '18 at 13:18
Richard
9,72721329
edited Nov 26 '18 at 13:18
Richard
9,72721329
edited Nov 26 '18 at 13:18
Richard
9,72721329
9,72721329
asked Nov 26 '18 at 4:28
LucyLucy
916
asked Nov 26 '18 at 4:28
LucyLucy
916
asked Nov 26 '18 at 4:28
LucyLucy
916
916
1
To get help please explain in words what the SQL is doing. Also provide sample input datasets and what the result dataset should be for that sample inputs.
– Tom
Nov 26 '18 at 5:43
you can use the same thing in proc sql except last where statment .you can chage it to app_dt between '01Jan2015'D and '30Jun2015'D
– Kiran
Nov 26 '18 at 11:44
1
What have you tried ? Do you know how toMERGEtwo data sets ? Do you know the data set optionin=?
– Richard
Nov 26 '18 at 13:20
add a comment |
1
To get help please explain in words what the SQL is doing. Also provide sample input datasets and what the result dataset should be for that sample inputs.
– Tom
Nov 26 '18 at 5:43
you can use the same thing in proc sql except last where statment .you can chage it to app_dt between '01Jan2015'D and '30Jun2015'D
– Kiran
Nov 26 '18 at 11:44
1
What have you tried ? Do you know how toMERGEtwo data sets ? Do you know the data set optionin=?
– Richard
Nov 26 '18 at 13:20
1
1
To get help please explain in words what the SQL is doing. Also provide sample input datasets and what the result dataset should be for that sample inputs.
– Tom
Nov 26 '18 at 5:43
To get help please explain in words what the SQL is doing. Also provide sample input datasets and what the result dataset should be for that sample inputs.
– Tom
Nov 26 '18 at 5:43
you can use the same thing in proc sql except last where statment .you can chage it to app_dt between '01Jan2015'D and '30Jun2015'D
– Kiran
Nov 26 '18 at 11:44
you can use the same thing in proc sql except last where statment .you can chage it to app_dt between '01Jan2015'D and '30Jun2015'D
– Kiran
Nov 26 '18 at 11:44
1
1
What have you tried ? Do you know how to
MERGE two data sets ? Do you know the data set option in= ?– Richard
Nov 26 '18 at 13:20
What have you tried ? Do you know how to
MERGE two data sets ? Do you know the data set option in= ?– Richard
Nov 26 '18 at 13:20
add a comment |
1 Answer
1
active
oldest
votes
1
rough attempt, untested because no data or such.
First merge and join on application number and then add the flag in another step. Or you could modify the SQL to work in PROC SQL which is a trivial exercise.
data part1;
merge tableA (in=A) tableB(in=B where=(appt_dt between '01Jan2015'd and '30Jun2015'd );
by application_no;
if a and b;
keep acct app_dt application_no;
run;
data part2;
merge part1 (in=p1) tableC (in=C);
by acc;
if p1;
if p1 and C then prom_ind=1;
else prom_ind=0;
run;
SAS SQL:
proc sql;
create table tableD nologging as
select
a.acct,
b.app_dt,
case when a.acct in (select acct from tableC) then 1 else 0 end prom_ind
from tableA a
inner join tableB b
on a.application_no = b.application_no
where b.app_dt between '01Jan2015'd and '30Jun2015'd ;
quit;
answered Nov 26 '18 at 20:05
ReezaReeza
13.4k21227
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53474788%2fwhat-is-the-data-step-equivalent-of-this-sql-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
rough attempt, untested because no data or such.
First merge and join on application number and then add the flag in another step. Or you could modify the SQL to work in PROC SQL which is a trivial exercise.
data part1;
merge tableA (in=A) tableB(in=B where=(appt_dt between '01Jan2015'd and '30Jun2015'd );
by application_no;
if a and b;
keep acct app_dt application_no;
run;
data part2;
merge part1 (in=p1) tableC (in=C);
by acc;
if p1;
if p1 and C then prom_ind=1;
else prom_ind=0;
run;
SAS SQL:
proc sql;
create table tableD nologging as
select
a.acct,
b.app_dt,
case when a.acct in (select acct from tableC) then 1 else 0 end prom_ind
from tableA a
inner join tableB b
on a.application_no = b.application_no
where b.app_dt between '01Jan2015'd and '30Jun2015'd ;
quit;
answered Nov 26 '18 at 20:05
ReezaReeza
13.4k21227
add a comment |
1
rough attempt, untested because no data or such.
First merge and join on application number and then add the flag in another step. Or you could modify the SQL to work in PROC SQL which is a trivial exercise.
data part1;
merge tableA (in=A) tableB(in=B where=(appt_dt between '01Jan2015'd and '30Jun2015'd );
by application_no;
if a and b;
keep acct app_dt application_no;
run;
data part2;
merge part1 (in=p1) tableC (in=C);
by acc;
if p1;
if p1 and C then prom_ind=1;
else prom_ind=0;
run;
SAS SQL:
proc sql;
create table tableD nologging as
select
a.acct,
b.app_dt,
case when a.acct in (select acct from tableC) then 1 else 0 end prom_ind
from tableA a
inner join tableB b
on a.application_no = b.application_no
where b.app_dt between '01Jan2015'd and '30Jun2015'd ;
quit;
answered Nov 26 '18 at 20:05
ReezaReeza
13.4k21227
add a comment |
1
1
1
rough attempt, untested because no data or such.
First merge and join on application number and then add the flag in another step. Or you could modify the SQL to work in PROC SQL which is a trivial exercise.
data part1;
merge tableA (in=A) tableB(in=B where=(appt_dt between '01Jan2015'd and '30Jun2015'd );
by application_no;
if a and b;
keep acct app_dt application_no;
run;
data part2;
merge part1 (in=p1) tableC (in=C);
by acc;
if p1;
if p1 and C then prom_ind=1;
else prom_ind=0;
run;
SAS SQL:
proc sql;
create table tableD nologging as
select
a.acct,
b.app_dt,
case when a.acct in (select acct from tableC) then 1 else 0 end prom_ind
from tableA a
inner join tableB b
on a.application_no = b.application_no
where b.app_dt between '01Jan2015'd and '30Jun2015'd ;
quit;
answered Nov 26 '18 at 20:05
ReezaReeza
13.4k21227
rough attempt, untested because no data or such.
First merge and join on application number and then add the flag in another step. Or you could modify the SQL to work in PROC SQL which is a trivial exercise.
data part1;
merge tableA (in=A) tableB(in=B where=(appt_dt between '01Jan2015'd and '30Jun2015'd );
by application_no;
if a and b;
keep acct app_dt application_no;
run;
data part2;
merge part1 (in=p1) tableC (in=C);
by acc;
if p1;
if p1 and C then prom_ind=1;
else prom_ind=0;
run;
SAS SQL:
proc sql;
create table tableD nologging as
select
a.acct,
b.app_dt,
case when a.acct in (select acct from tableC) then 1 else 0 end prom_ind
from tableA a
inner join tableB b
on a.application_no = b.application_no
where b.app_dt between '01Jan2015'd and '30Jun2015'd ;
quit;
answered Nov 26 '18 at 20:05
ReezaReeza
13.4k21227
answered Nov 26 '18 at 20:05
ReezaReeza
13.4k21227
answered Nov 26 '18 at 20:05
ReezaReeza
13.4k21227
answered Nov 26 '18 at 20:05
ReezaReeza
13.4k21227
13.4k21227
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53474788%2fwhat-is-the-data-step-equivalent-of-this-sql-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
To get help please explain in words what the SQL is doing. Also provide sample input datasets and what the result dataset should be for that sample inputs.
– Tom
Nov 26 '18 at 5:43
you can use the same thing in proc sql except last where statment .you can chage it to app_dt between '01Jan2015'D and '30Jun2015'D
– Kiran
Nov 26 '18 at 11:44
1
What have you tried ? Do you know how to
MERGEtwo data sets ? Do you know the data set optionin=?– Richard
Nov 26 '18 at 13:20