Mixing
Sql equivalent in SAS
I have a code such as below in sql(lot more and and not ins but just wanted to list few) i am new to sas and know proc sql a bit etc, learning and exploring everyday,
Select * from table
Where date=‘20180112’
and type=‘apple’ and location=‘dc’ and not
(columnName)in(‘a’,’b’) And lat=‘ten’
I am not able to understand sas equivalent of above sql as below. Can someone please explain sas code of if part and then do
Data sample;
Set sourcetble;
If date=‘20180112’ and type=‘apple’
And location=‘dc’ then do;
Blah1=‘rain’
Blah2=‘something else’
If columnName in(‘a’, ‘b’) and lat=‘ten’ Then do;
sql sas
edited Nov 20 '18 at 1:55
Reeza
13.1k21226
asked Nov 19 '18 at 23:33
BritaBrita
164
add a comment |
I have a code such as below in sql(lot more and and not ins but just wanted to list few) i am new to sas and know proc sql a bit etc, learning and exploring everyday,
Select * from table
Where date=‘20180112’
and type=‘apple’ and location=‘dc’ and not
(columnName)in(‘a’,’b’) And lat=‘ten’
I am not able to understand sas equivalent of above sql as below. Can someone please explain sas code of if part and then do
Data sample;
Set sourcetble;
If date=‘20180112’ and type=‘apple’
And location=‘dc’ then do;
Blah1=‘rain’
Blah2=‘something else’
If columnName in(‘a’, ‘b’) and lat=‘ten’ Then do;
sql sas
edited Nov 20 '18 at 1:55
Reeza
13.1k21226
asked Nov 19 '18 at 23:33
BritaBrita
164
SAS has SQL built in. See PROC SQL. You just need a semicolon.
– data _null_
Nov 19 '18 at 23:58
I do not understand what your question is. Do you want to see a data step equivalent for the select statement you posted? Or do you want a description of what the data step code is doing? Note that in their current form they are not really close to being equivalent.
– Tom
Nov 20 '18 at 1:17
Neither of those are the full code and they're not necessarily equivalent depending on where the END is for the data step.
– Reeza
Nov 20 '18 at 1:55
add a comment |
I have a code such as below in sql(lot more and and not ins but just wanted to list few) i am new to sas and know proc sql a bit etc, learning and exploring everyday,
Select * from table
Where date=‘20180112’
and type=‘apple’ and location=‘dc’ and not
(columnName)in(‘a’,’b’) And lat=‘ten’
I am not able to understand sas equivalent of above sql as below. Can someone please explain sas code of if part and then do
Data sample;
Set sourcetble;
If date=‘20180112’ and type=‘apple’
And location=‘dc’ then do;
Blah1=‘rain’
Blah2=‘something else’
If columnName in(‘a’, ‘b’) and lat=‘ten’ Then do;
sql sas
edited Nov 20 '18 at 1:55
Reeza
13.1k21226
asked Nov 19 '18 at 23:33
BritaBrita
164
I have a code such as below in sql(lot more and and not ins but just wanted to list few) i am new to sas and know proc sql a bit etc, learning and exploring everyday,
Select * from table
Where date=‘20180112’
and type=‘apple’ and location=‘dc’ and not
(columnName)in(‘a’,’b’) And lat=‘ten’
I am not able to understand sas equivalent of above sql as below. Can someone please explain sas code of if part and then do
Data sample;
Set sourcetble;
If date=‘20180112’ and type=‘apple’
And location=‘dc’ then do;
Blah1=‘rain’
Blah2=‘something else’
If columnName in(‘a’, ‘b’) and lat=‘ten’ Then do;
sql sas
sql sas
edited Nov 20 '18 at 1:55
Reeza
13.1k21226
asked Nov 19 '18 at 23:33
BritaBrita
164
edited Nov 20 '18 at 1:55
Reeza
13.1k21226
asked Nov 19 '18 at 23:33
BritaBrita
164
edited Nov 20 '18 at 1:55
Reeza
13.1k21226
edited Nov 20 '18 at 1:55
Reeza
13.1k21226
edited Nov 20 '18 at 1:55
Reeza
13.1k21226
13.1k21226
asked Nov 19 '18 at 23:33
BritaBrita
164
asked Nov 19 '18 at 23:33
BritaBrita
164
asked Nov 19 '18 at 23:33
BritaBrita
164
164
SAS has SQL built in. See PROC SQL. You just need a semicolon.
– data _null_
Nov 19 '18 at 23:58
I do not understand what your question is. Do you want to see a data step equivalent for the select statement you posted? Or do you want a description of what the data step code is doing? Note that in their current form they are not really close to being equivalent.
– Tom
Nov 20 '18 at 1:17
Neither of those are the full code and they're not necessarily equivalent depending on where the END is for the data step.
– Reeza
Nov 20 '18 at 1:55
add a comment |
SAS has SQL built in. See PROC SQL. You just need a semicolon.
– data _null_
Nov 19 '18 at 23:58
I do not understand what your question is. Do you want to see a data step equivalent for the select statement you posted? Or do you want a description of what the data step code is doing? Note that in their current form they are not really close to being equivalent.
– Tom
Nov 20 '18 at 1:17
Neither of those are the full code and they're not necessarily equivalent depending on where the END is for the data step.
– Reeza
Nov 20 '18 at 1:55
SAS has SQL built in. See PROC SQL. You just need a semicolon.
– data _null_
Nov 19 '18 at 23:58
SAS has SQL built in. See PROC SQL. You just need a semicolon.
– data _null_
Nov 19 '18 at 23:58
I do not understand what your question is. Do you want to see a data step equivalent for the select statement you posted? Or do you want a description of what the data step code is doing? Note that in their current form they are not really close to being equivalent.
– Tom
Nov 20 '18 at 1:17
I do not understand what your question is. Do you want to see a data step equivalent for the select statement you posted? Or do you want a description of what the data step code is doing? Note that in their current form they are not really close to being equivalent.
– Tom
Nov 20 '18 at 1:17
Neither of those are the full code and they're not necessarily equivalent depending on where the END is for the data step.
– Reeza
Nov 20 '18 at 1:55
Neither of those are the full code and they're not necessarily equivalent depending on where the END is for the data step.
– Reeza
Nov 20 '18 at 1:55
add a comment |
3 Answers
3
active
oldest
votes
This just subsets based the values and variables in the WHERE statement.
Data sample;
set table;
WHERE date='20180112' and type='apple' And location='dc'
and columnName in (‘a’, ‘b’) and lat=‘ten’;
<other optional code>;
run;
answered Nov 20 '18 at 0:05
data _null_data _null_
5,217610
Sorry i for incomplete code but i basically want sql code’s equivalent in sas and description of what the data step is doing
– Brita
Nov 20 '18 at 5:23
@Brita This data step IS the equivalent of your SQL statement. The data step code in your question is doing something totally different than the SQL statement.
– Tom
Nov 20 '18 at 14:29
add a comment |
Not like SQL query, a SAS data step will result in creating a new dataset. If you don't need to have a new dataset, you can use "data _null_;". Alternatively there are SAS procedures that will simply display dataset such as SQL "select" would do.
The "set" in SAS is equivalent to the "from" in SQL: it specifies the base dataset(s) from which you build the new dataset.
By default, SAS data step keeps all variables of the "set" datasets. It is equivalent to "select *" in SQL. If you need only some variables, you can use "keep" and "drop" statements in SAS.
The "where" clause and "and"/"or" operators work similarly in SAS and SQL, but with slightly different syntax.
answered Nov 20 '18 at 12:02
FloTFloT
20919
add a comment |
The if … then in the data step has no correspondce to the SQL shown in the question. A conditional assignment in SQL is done using a case statement.
So a DATA step statement such as
data want;
set have;
…
if date="20180112" and type="apple" and location="dc" then do;
Blah1="rain";
Blah2="something else";
end;
would be concordant with SQL
Proc SQL;
create table want as
select …
, case when date="20180112" and type="apple" and location="dc"
then "rain"
else ""
end as Blah1
, case when date="20180112" and type="apple" and location="dc"
then "something else"
else ""
end as Blah2
from
have
…
;
For the case of some algorithm needing to assign several variables at once when some criteria (if logic) is met:
- DATA Step has
do; … end;syntax which can have several assignments statements within. - SQL select statement can only assign one variable per logic evaluation (
case statement), thus the logic code has to be repeated for each variable being assigned based on criteria.
answered Nov 20 '18 at 13:09
RichardRichard
8,35421227
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
});
}
});
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%2f53384169%2fsql-equivalent-in-sas%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
This just subsets based the values and variables in the WHERE statement.
Data sample;
set table;
WHERE date='20180112' and type='apple' And location='dc'
and columnName in (‘a’, ‘b’) and lat=‘ten’;
<other optional code>;
run;
answered Nov 20 '18 at 0:05
data _null_data _null_
5,217610
Sorry i for incomplete code but i basically want sql code’s equivalent in sas and description of what the data step is doing
– Brita
Nov 20 '18 at 5:23
@Brita This data step IS the equivalent of your SQL statement. The data step code in your question is doing something totally different than the SQL statement.
– Tom
Nov 20 '18 at 14:29
add a comment |
This just subsets based the values and variables in the WHERE statement.
Data sample;
set table;
WHERE date='20180112' and type='apple' And location='dc'
and columnName in (‘a’, ‘b’) and lat=‘ten’;
<other optional code>;
run;
answered Nov 20 '18 at 0:05
data _null_data _null_
5,217610
Sorry i for incomplete code but i basically want sql code’s equivalent in sas and description of what the data step is doing
– Brita
Nov 20 '18 at 5:23
@Brita This data step IS the equivalent of your SQL statement. The data step code in your question is doing something totally different than the SQL statement.
– Tom
Nov 20 '18 at 14:29
add a comment |
This just subsets based the values and variables in the WHERE statement.
Data sample;
set table;
WHERE date='20180112' and type='apple' And location='dc'
and columnName in (‘a’, ‘b’) and lat=‘ten’;
<other optional code>;
run;
answered Nov 20 '18 at 0:05
data _null_data _null_
5,217610
This just subsets based the values and variables in the WHERE statement.
Data sample;
set table;
WHERE date='20180112' and type='apple' And location='dc'
and columnName in (‘a’, ‘b’) and lat=‘ten’;
<other optional code>;
run;
answered Nov 20 '18 at 0:05
data _null_data _null_
5,217610
answered Nov 20 '18 at 0:05
data _null_data _null_
5,217610
answered Nov 20 '18 at 0:05
data _null_data _null_
5,217610
answered Nov 20 '18 at 0:05
data _null_data _null_
5,217610
5,217610
Sorry i for incomplete code but i basically want sql code’s equivalent in sas and description of what the data step is doing
– Brita
Nov 20 '18 at 5:23
@Brita This data step IS the equivalent of your SQL statement. The data step code in your question is doing something totally different than the SQL statement.
– Tom
Nov 20 '18 at 14:29
add a comment |
Sorry i for incomplete code but i basically want sql code’s equivalent in sas and description of what the data step is doing
– Brita
Nov 20 '18 at 5:23
@Brita This data step IS the equivalent of your SQL statement. The data step code in your question is doing something totally different than the SQL statement.
– Tom
Nov 20 '18 at 14:29
Sorry i for incomplete code but i basically want sql code’s equivalent in sas and description of what the data step is doing
– Brita
Nov 20 '18 at 5:23
Sorry i for incomplete code but i basically want sql code’s equivalent in sas and description of what the data step is doing
– Brita
Nov 20 '18 at 5:23
@Brita This data step IS the equivalent of your SQL statement. The data step code in your question is doing something totally different than the SQL statement.
– Tom
Nov 20 '18 at 14:29
@Brita This data step IS the equivalent of your SQL statement. The data step code in your question is doing something totally different than the SQL statement.
– Tom
Nov 20 '18 at 14:29
add a comment |
Not like SQL query, a SAS data step will result in creating a new dataset. If you don't need to have a new dataset, you can use "data _null_;". Alternatively there are SAS procedures that will simply display dataset such as SQL "select" would do.
The "set" in SAS is equivalent to the "from" in SQL: it specifies the base dataset(s) from which you build the new dataset.
By default, SAS data step keeps all variables of the "set" datasets. It is equivalent to "select *" in SQL. If you need only some variables, you can use "keep" and "drop" statements in SAS.
The "where" clause and "and"/"or" operators work similarly in SAS and SQL, but with slightly different syntax.
answered Nov 20 '18 at 12:02
FloTFloT
20919
add a comment |
Not like SQL query, a SAS data step will result in creating a new dataset. If you don't need to have a new dataset, you can use "data _null_;". Alternatively there are SAS procedures that will simply display dataset such as SQL "select" would do.
The "set" in SAS is equivalent to the "from" in SQL: it specifies the base dataset(s) from which you build the new dataset.
By default, SAS data step keeps all variables of the "set" datasets. It is equivalent to "select *" in SQL. If you need only some variables, you can use "keep" and "drop" statements in SAS.
The "where" clause and "and"/"or" operators work similarly in SAS and SQL, but with slightly different syntax.
answered Nov 20 '18 at 12:02
FloTFloT
20919
add a comment |
Not like SQL query, a SAS data step will result in creating a new dataset. If you don't need to have a new dataset, you can use "data _null_;". Alternatively there are SAS procedures that will simply display dataset such as SQL "select" would do.
The "set" in SAS is equivalent to the "from" in SQL: it specifies the base dataset(s) from which you build the new dataset.
By default, SAS data step keeps all variables of the "set" datasets. It is equivalent to "select *" in SQL. If you need only some variables, you can use "keep" and "drop" statements in SAS.
The "where" clause and "and"/"or" operators work similarly in SAS and SQL, but with slightly different syntax.
answered Nov 20 '18 at 12:02
FloTFloT
20919
Not like SQL query, a SAS data step will result in creating a new dataset. If you don't need to have a new dataset, you can use "data _null_;". Alternatively there are SAS procedures that will simply display dataset such as SQL "select" would do.
The "set" in SAS is equivalent to the "from" in SQL: it specifies the base dataset(s) from which you build the new dataset.
By default, SAS data step keeps all variables of the "set" datasets. It is equivalent to "select *" in SQL. If you need only some variables, you can use "keep" and "drop" statements in SAS.
The "where" clause and "and"/"or" operators work similarly in SAS and SQL, but with slightly different syntax.
answered Nov 20 '18 at 12:02
FloTFloT
20919
answered Nov 20 '18 at 12:02
FloTFloT
20919
answered Nov 20 '18 at 12:02
FloTFloT
20919
answered Nov 20 '18 at 12:02
FloTFloT
20919
20919
add a comment |
add a comment |
The if … then in the data step has no correspondce to the SQL shown in the question. A conditional assignment in SQL is done using a case statement.
So a DATA step statement such as
data want;
set have;
…
if date="20180112" and type="apple" and location="dc" then do;
Blah1="rain";
Blah2="something else";
end;
would be concordant with SQL
Proc SQL;
create table want as
select …
, case when date="20180112" and type="apple" and location="dc"
then "rain"
else ""
end as Blah1
, case when date="20180112" and type="apple" and location="dc"
then "something else"
else ""
end as Blah2
from
have
…
;
For the case of some algorithm needing to assign several variables at once when some criteria (if logic) is met:
- DATA Step has
do; … end;syntax which can have several assignments statements within. - SQL select statement can only assign one variable per logic evaluation (
case statement), thus the logic code has to be repeated for each variable being assigned based on criteria.
answered Nov 20 '18 at 13:09
RichardRichard
8,35421227
add a comment |
The if … then in the data step has no correspondce to the SQL shown in the question. A conditional assignment in SQL is done using a case statement.
So a DATA step statement such as
data want;
set have;
…
if date="20180112" and type="apple" and location="dc" then do;
Blah1="rain";
Blah2="something else";
end;
would be concordant with SQL
Proc SQL;
create table want as
select …
, case when date="20180112" and type="apple" and location="dc"
then "rain"
else ""
end as Blah1
, case when date="20180112" and type="apple" and location="dc"
then "something else"
else ""
end as Blah2
from
have
…
;
For the case of some algorithm needing to assign several variables at once when some criteria (if logic) is met:
- DATA Step has
do; … end;syntax which can have several assignments statements within. - SQL select statement can only assign one variable per logic evaluation (
case statement), thus the logic code has to be repeated for each variable being assigned based on criteria.
answered Nov 20 '18 at 13:09
RichardRichard
8,35421227
add a comment |
The if … then in the data step has no correspondce to the SQL shown in the question. A conditional assignment in SQL is done using a case statement.
So a DATA step statement such as
data want;
set have;
…
if date="20180112" and type="apple" and location="dc" then do;
Blah1="rain";
Blah2="something else";
end;
would be concordant with SQL
Proc SQL;
create table want as
select …
, case when date="20180112" and type="apple" and location="dc"
then "rain"
else ""
end as Blah1
, case when date="20180112" and type="apple" and location="dc"
then "something else"
else ""
end as Blah2
from
have
…
;
For the case of some algorithm needing to assign several variables at once when some criteria (if logic) is met:
- DATA Step has
do; … end;syntax which can have several assignments statements within. - SQL select statement can only assign one variable per logic evaluation (
case statement), thus the logic code has to be repeated for each variable being assigned based on criteria.
answered Nov 20 '18 at 13:09
RichardRichard
8,35421227
The if … then in the data step has no correspondce to the SQL shown in the question. A conditional assignment in SQL is done using a case statement.
So a DATA step statement such as
data want;
set have;
…
if date="20180112" and type="apple" and location="dc" then do;
Blah1="rain";
Blah2="something else";
end;
would be concordant with SQL
Proc SQL;
create table want as
select …
, case when date="20180112" and type="apple" and location="dc"
then "rain"
else ""
end as Blah1
, case when date="20180112" and type="apple" and location="dc"
then "something else"
else ""
end as Blah2
from
have
…
;
For the case of some algorithm needing to assign several variables at once when some criteria (if logic) is met:
- DATA Step has
do; … end;syntax which can have several assignments statements within. - SQL select statement can only assign one variable per logic evaluation (
case statement), thus the logic code has to be repeated for each variable being assigned based on criteria.
answered Nov 20 '18 at 13:09
RichardRichard
8,35421227
answered Nov 20 '18 at 13:09
RichardRichard
8,35421227
answered Nov 20 '18 at 13:09
RichardRichard
8,35421227
answered Nov 20 '18 at 13:09
RichardRichard
8,35421227
8,35421227
add a comment |
add a comment |
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.
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%2f53384169%2fsql-equivalent-in-sas%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
SAS has SQL built in. See PROC SQL. You just need a semicolon.
– data _null_
Nov 19 '18 at 23:58
I do not understand what your question is. Do you want to see a data step equivalent for the select statement you posted? Or do you want a description of what the data step code is doing? Note that in their current form they are not really close to being equivalent.
– Tom
Nov 20 '18 at 1:17
Neither of those are the full code and they're not necessarily equivalent depending on where the END is for the data step.
– Reeza
Nov 20 '18 at 1:55