what is the difference between T sql and and Proc sqlSAS proc sql concatenation and join likeInsert Date Field to MS SQL from Proc SQLChanging SAS ODS contentitem for PROC SQL SELECT statementUsing PROC SQL in SAS to transpose a table from long to wideproc sql aggregateSAS conditional execution of proc sqlHash join equivalent on PROC SQL betweenDifference between SQLite and PROC SQL when evaluating blank spaceSAS (proc sql) count number of records if difference between two date variables greater of 3 yearsPROC SQL LEFT JOIN
How can ping know if my host is down
Multiplicative persistence
A Trivial Diagnosis
Giving feedback to someone without sounding prejudiced
Can I say "fingers" when referring to toes?
What is the English pronunciation of "pain au chocolat"?
Strong empirical falsification of quantum mechanics based on vacuum energy density?
Has any country ever had 2 former presidents in jail simultaneously?
Can I cause damage to electrical appliances by unplugging them when they are turned on?
Why does AES have exactly 10 rounds for a 128-bit key, 12 for 192 bits and 14 for a 256-bit key size?
How to convince somebody that he is fit for something else, but not this job?
How could a planet have erratic days?
Why do ¬, ∀ and ∃ have the same precedence?
I found an audio circuit and I built it just fine, but I find it a bit too quiet. How do I amplify the output so that it is a bit louder?
How do I fix the group tension caused by my character stealing and possibly killing without provocation?
Is there a nicer/politer/more positive alternative for "negates"?
Is there any evidence that Cleopatra and Caesarion considered fleeing to India to escape the Romans?
Does "he squandered his car on drink" sound natural?
Doesn't the system of the Supreme Court oppose justice?
"Oh no!" in Latin
Why is the Sun approximated as a black body at ~ 5800 K?
Can you use Vicious Mockery to win an argument or gain favours?
How to make money from a browser who sees 5 seconds into the future of any web page?
Is there a RAID 0 Equivalent for RAM?
what is the difference between T sql and and Proc sql
SAS proc sql concatenation and join likeInsert Date Field to MS SQL from Proc SQLChanging SAS ODS contentitem for PROC SQL SELECT statementUsing PROC SQL in SAS to transpose a table from long to wideproc sql aggregateSAS conditional execution of proc sqlHash join equivalent on PROC SQL betweenDifference between SQLite and PROC SQL when evaluating blank spaceSAS (proc sql) count number of records if difference between two date variables greater of 3 yearsPROC SQL LEFT JOIN
Can someone please help me with solution?
sample1:
ReportMonthYear ProductItemCode ClosedDate PriorMonthCalendarDate
20160331 1234 24Mar2016 0:00:00 20160201
sample2:
ReportMonthYear ProductItemCode OpenDate ClosedDate OverDraftExpiryDate
20160229 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
20150930 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
20151130 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
20150731 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
20150831 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
20160131 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
20160331 1234 13Mar2015 0:00:00 24Mar2016 0:00:00
20151231 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
20151031 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
I am getting correct results when I use T-SQL but not by Proc SQL.
output required:
ProductItemCode ReportMonthYear PriorMonthCalendarDate ClosedDate OverDraftExpiryDate earlyclosuremonths
1234 20160331 20160229 2016-03-24 00:00:00 28Oct2016 0:00:00 7
code in T sql:
select
cd.ProductItemCode,
cd.ReportMonthYear,
cd.PriorMonthYear,
cd.ClosedDate,
df.OverDraftExpiryDate,
datediff(m,cd.ClosedDate,OverDraftExpiryDate) EarlyClosureMonths
from
sample1 cd
inner join sample2 df
on cd.ProductItemCode = df.ProductItemCode
and cd.PriorMonthYear = df.ReportMonthYear
code in Proc sql;
proc sql;
select
cd.ProductItemCode,
cd.ReportMonthYear,
cd.PriorMonthCalendarDate,
cd.ClosedDate,
df.OverDraftExpiryDate,
intck('month',cd.ClosedDate,df.OverDraftExpiryDate) as EarlyClosureMonths
from
sample1 cd
inner join sample2 df
on cd.ProductItemCode = df.ProductItemCode
and cd.PriorMonthCalendarDate = df.ReportMonthYear;
quit;
sas
edited Mar 8 at 1:16
Tom
24.3k2720
asked Mar 8 at 0:03
RamuRamu
637
add a comment |
Can someone please help me with solution?
sample1:
ReportMonthYear ProductItemCode ClosedDate PriorMonthCalendarDate
20160331 1234 24Mar2016 0:00:00 20160201
sample2:
ReportMonthYear ProductItemCode OpenDate ClosedDate OverDraftExpiryDate
20160229 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
20150930 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
20151130 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
20150731 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
20150831 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
20160131 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
20160331 1234 13Mar2015 0:00:00 24Mar2016 0:00:00
20151231 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
20151031 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
I am getting correct results when I use T-SQL but not by Proc SQL.
output required:
ProductItemCode ReportMonthYear PriorMonthCalendarDate ClosedDate OverDraftExpiryDate earlyclosuremonths
1234 20160331 20160229 2016-03-24 00:00:00 28Oct2016 0:00:00 7
code in T sql:
select
cd.ProductItemCode,
cd.ReportMonthYear,
cd.PriorMonthYear,
cd.ClosedDate,
df.OverDraftExpiryDate,
datediff(m,cd.ClosedDate,OverDraftExpiryDate) EarlyClosureMonths
from
sample1 cd
inner join sample2 df
on cd.ProductItemCode = df.ProductItemCode
and cd.PriorMonthYear = df.ReportMonthYear
code in Proc sql;
proc sql;
select
cd.ProductItemCode,
cd.ReportMonthYear,
cd.PriorMonthCalendarDate,
cd.ClosedDate,
df.OverDraftExpiryDate,
intck('month',cd.ClosedDate,df.OverDraftExpiryDate) as EarlyClosureMonths
from
sample1 cd
inner join sample2 df
on cd.ProductItemCode = df.ProductItemCode
and cd.PriorMonthCalendarDate = df.ReportMonthYear;
quit;
sas
edited Mar 8 at 1:16
Tom
24.3k2720
asked Mar 8 at 0:03
RamuRamu
637
Why are you using a date interval for values that have datetime values?
– Tom
Mar 8 at 1:17
HI Tom, thank u for responding. I have considered date part only.sorry I forgot to take out time part. I tried but could not get answer. Can you please tell me how to get exact result?
– Ramu
Mar 9 at 6:17
add a comment |
Can someone please help me with solution?
sample1:
ReportMonthYear ProductItemCode ClosedDate PriorMonthCalendarDate
20160331 1234 24Mar2016 0:00:00 20160201
sample2:
ReportMonthYear ProductItemCode OpenDate ClosedDate OverDraftExpiryDate
20160229 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
20150930 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
20151130 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
20150731 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
20150831 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
20160131 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
20160331 1234 13Mar2015 0:00:00 24Mar2016 0:00:00
20151231 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
20151031 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
I am getting correct results when I use T-SQL but not by Proc SQL.
output required:
ProductItemCode ReportMonthYear PriorMonthCalendarDate ClosedDate OverDraftExpiryDate earlyclosuremonths
1234 20160331 20160229 2016-03-24 00:00:00 28Oct2016 0:00:00 7
code in T sql:
select
cd.ProductItemCode,
cd.ReportMonthYear,
cd.PriorMonthYear,
cd.ClosedDate,
df.OverDraftExpiryDate,
datediff(m,cd.ClosedDate,OverDraftExpiryDate) EarlyClosureMonths
from
sample1 cd
inner join sample2 df
on cd.ProductItemCode = df.ProductItemCode
and cd.PriorMonthYear = df.ReportMonthYear
code in Proc sql;
proc sql;
select
cd.ProductItemCode,
cd.ReportMonthYear,
cd.PriorMonthCalendarDate,
cd.ClosedDate,
df.OverDraftExpiryDate,
intck('month',cd.ClosedDate,df.OverDraftExpiryDate) as EarlyClosureMonths
from
sample1 cd
inner join sample2 df
on cd.ProductItemCode = df.ProductItemCode
and cd.PriorMonthCalendarDate = df.ReportMonthYear;
quit;
sas
edited Mar 8 at 1:16
Tom
24.3k2720
asked Mar 8 at 0:03
RamuRamu
637
Can someone please help me with solution?
sample1:
ReportMonthYear ProductItemCode ClosedDate PriorMonthCalendarDate
20160331 1234 24Mar2016 0:00:00 20160201
sample2:
ReportMonthYear ProductItemCode OpenDate ClosedDate OverDraftExpiryDate
20160229 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
20150930 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
20151130 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
20150731 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
20150831 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
20160131 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
20160331 1234 13Mar2015 0:00:00 24Mar2016 0:00:00
20151231 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
20151031 1234 13Mar2015 0:00:00 28Oct2016 0:00:00
I am getting correct results when I use T-SQL but not by Proc SQL.
output required:
ProductItemCode ReportMonthYear PriorMonthCalendarDate ClosedDate OverDraftExpiryDate earlyclosuremonths
1234 20160331 20160229 2016-03-24 00:00:00 28Oct2016 0:00:00 7
code in T sql:
select
cd.ProductItemCode,
cd.ReportMonthYear,
cd.PriorMonthYear,
cd.ClosedDate,
df.OverDraftExpiryDate,
datediff(m,cd.ClosedDate,OverDraftExpiryDate) EarlyClosureMonths
from
sample1 cd
inner join sample2 df
on cd.ProductItemCode = df.ProductItemCode
and cd.PriorMonthYear = df.ReportMonthYear
code in Proc sql;
proc sql;
select
cd.ProductItemCode,
cd.ReportMonthYear,
cd.PriorMonthCalendarDate,
cd.ClosedDate,
df.OverDraftExpiryDate,
intck('month',cd.ClosedDate,df.OverDraftExpiryDate) as EarlyClosureMonths
from
sample1 cd
inner join sample2 df
on cd.ProductItemCode = df.ProductItemCode
and cd.PriorMonthCalendarDate = df.ReportMonthYear;
quit;
sas
sas
edited Mar 8 at 1:16
Tom
24.3k2720
asked Mar 8 at 0:03
RamuRamu
637
edited Mar 8 at 1:16
Tom
24.3k2720
asked Mar 8 at 0:03
RamuRamu
637
edited Mar 8 at 1:16
Tom
24.3k2720
edited Mar 8 at 1:16
Tom
24.3k2720
edited Mar 8 at 1:16
Tom
24.3k2720
24.3k2720
asked Mar 8 at 0:03
RamuRamu
637
asked Mar 8 at 0:03
RamuRamu
637
asked Mar 8 at 0:03
RamuRamu
637
637
Why are you using a date interval for values that have datetime values?
– Tom
Mar 8 at 1:17
HI Tom, thank u for responding. I have considered date part only.sorry I forgot to take out time part. I tried but could not get answer. Can you please tell me how to get exact result?
– Ramu
Mar 9 at 6:17
add a comment |
Why are you using a date interval for values that have datetime values?
– Tom
Mar 8 at 1:17
HI Tom, thank u for responding. I have considered date part only.sorry I forgot to take out time part. I tried but could not get answer. Can you please tell me how to get exact result?
– Ramu
Mar 9 at 6:17
Why are you using a date interval for values that have datetime values?
– Tom
Mar 8 at 1:17
Why are you using a date interval for values that have datetime values?
– Tom
Mar 8 at 1:17
HI Tom, thank u for responding. I have considered date part only.sorry I forgot to take out time part. I tried but could not get answer. Can you please tell me how to get exact result?
– Ramu
Mar 9 at 6:17
HI Tom, thank u for responding. I have considered date part only.sorry I forgot to take out time part. I tried but could not get answer. Can you please tell me how to get exact result?
– Ramu
Mar 9 at 6:17
add a comment |
1 Answer
1
active
oldest
votes
Looks like your "date" variables actually have datetime values instead date values. Dates are stored as the number of days since 1960 and datetimes are stored as the number of seconds instead.
If you calculate months on values that are in seconds instead of days you will get the wrong answer. Use the dtmonth interval instead.
intck('dtmonth',cd.ClosedDate,df.OverDraftExpiryDate) as EarlyClosureMonths
Or convert your datetime values to dates and use the month interval.
intck('month',datepart(cd.ClosedDate),datepart(df.OverDraftExpiryDate)) as EarlyClosureMonths
answered Mar 8 at 1:13
TomTom
24.3k2720
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%2f55054781%2fwhat-is-the-difference-between-t-sql-and-and-proc-sql%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
Looks like your "date" variables actually have datetime values instead date values. Dates are stored as the number of days since 1960 and datetimes are stored as the number of seconds instead.
If you calculate months on values that are in seconds instead of days you will get the wrong answer. Use the dtmonth interval instead.
intck('dtmonth',cd.ClosedDate,df.OverDraftExpiryDate) as EarlyClosureMonths
Or convert your datetime values to dates and use the month interval.
intck('month',datepart(cd.ClosedDate),datepart(df.OverDraftExpiryDate)) as EarlyClosureMonths
answered Mar 8 at 1:13
TomTom
24.3k2720
add a comment |
Looks like your "date" variables actually have datetime values instead date values. Dates are stored as the number of days since 1960 and datetimes are stored as the number of seconds instead.
If you calculate months on values that are in seconds instead of days you will get the wrong answer. Use the dtmonth interval instead.
intck('dtmonth',cd.ClosedDate,df.OverDraftExpiryDate) as EarlyClosureMonths
Or convert your datetime values to dates and use the month interval.
intck('month',datepart(cd.ClosedDate),datepart(df.OverDraftExpiryDate)) as EarlyClosureMonths
answered Mar 8 at 1:13
TomTom
24.3k2720
add a comment |
Looks like your "date" variables actually have datetime values instead date values. Dates are stored as the number of days since 1960 and datetimes are stored as the number of seconds instead.
If you calculate months on values that are in seconds instead of days you will get the wrong answer. Use the dtmonth interval instead.
intck('dtmonth',cd.ClosedDate,df.OverDraftExpiryDate) as EarlyClosureMonths
Or convert your datetime values to dates and use the month interval.
intck('month',datepart(cd.ClosedDate),datepart(df.OverDraftExpiryDate)) as EarlyClosureMonths
answered Mar 8 at 1:13
TomTom
24.3k2720
Looks like your "date" variables actually have datetime values instead date values. Dates are stored as the number of days since 1960 and datetimes are stored as the number of seconds instead.
If you calculate months on values that are in seconds instead of days you will get the wrong answer. Use the dtmonth interval instead.
intck('dtmonth',cd.ClosedDate,df.OverDraftExpiryDate) as EarlyClosureMonths
Or convert your datetime values to dates and use the month interval.
intck('month',datepart(cd.ClosedDate),datepart(df.OverDraftExpiryDate)) as EarlyClosureMonths
answered Mar 8 at 1:13
TomTom
24.3k2720
edited Mar 9 at 19:58
answered Mar 8 at 1:13
TomTom
24.3k2720
answered Mar 8 at 1:13
TomTom
24.3k2720
answered Mar 8 at 1:13
TomTom
24.3k2720
24.3k2720
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%2f55054781%2fwhat-is-the-difference-between-t-sql-and-and-proc-sql%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
Why are you using a date interval for values that have datetime values?
– Tom
Mar 8 at 1:17
HI Tom, thank u for responding. I have considered date part only.sorry I forgot to take out time part. I tried but could not get answer. Can you please tell me how to get exact result?
– Ramu
Mar 9 at 6:17