About hvempati

hvempati · ‎03-12-2019

@novinosrin I know you have a very high standards set for yourself. But because of that, Novice SAS user can learn a bit more about hash function. I am in fact glad that you had posted for me to learn how it works. Thank you so much!! 😄

hvempati · ‎03-12-2019

@novinosrin Thank you so much!!!! It works like a charm!!! But is it looping inside the data step!!! Even though it works well, I still did not get the logic as to how is it working.? Please excuse me if I am asking a very basic doubt. Like if _n_=1, it means the 1st row correct? like if _n_=1, then put that row a output and put k=dts Am I correct? How is dts going to the next value is what I don't understand. Is this almost equal to looping? Thank you.

hvempati · ‎03-12-2019

sorry If it was not clear but data want; set WORK.cards; days_between = intck('days',dts,lag(dts)); run; is the code that gives the value

hvempati · ‎03-12-2019

I need to get all the dates where the difference between the previous dates is 30. ex. data cards; input id dts date9.; format dts date9.; datalines; 1 02JAN2017 2 07JAN2017 3 14JAN2017 4 21JAN2017 5 28JAN2017 6 04FEB2017 7 14FEB2017 8 31MAR2017 9 01APR2017 10 03JUN2017 11 10JUN2017 12 01JUL2017 13 08JUL2017 14 23NOV2017 15 14MAR2018 16 02APR2018 17 02MAY2018 18 05MAY2018 ; run; with the code below data want; set WORK.cards; days_between = intck('days',dts,lag(dts)); run; I can get the difference with the previous date and can filter if it is less than 30.(since the days_between are -ve) But my desired output should be all dates with greater than 30 days. i.e dts 02Jan2017 04Feb2017 31Mar2017 03Jun2017 08Jul2017 23Nov2017 14Mar2018 02May2018 So basically the count resets to 0 after it finds a date. I can do this easily with loops in Python. I am not sure as how to do it in SAS. Any help is highly Appreciated Thank you.

hvempati · ‎02-21-2019

I have a data with numbers like this - 4259311 4262311 18730311 3000001311 3000004311 3000011311 3000013311 3000019311 I get these numbers by joining two rows and have a variable name uni_num I write a macro to loop over all the numbers and create temp table to manipulate each set. But while running the code it does well for all numbers which have length less than 8 but does not detect the numbers such as 3000001311. proc sql; create table temp_1&i as select * from work.input_for where uni_num = &var. ; quit; where my &var is the number above. I was trying to change the length of number format but with no luck. Can anyone help me with this? Thank you.

hvempati · ‎10-12-2018

But is there not a way to do this type of join or can I split the data and then join?

hvempati · ‎10-12-2018

I have two tables. Table A id start_dt end_dt 15 05Sep2018 12-Sep-18 15 10Sep2018 20-Sep-18 15 11Sep2018 12-Sep-18 16 05Sep2018 20-Sep-18 17 06Sep2018 6-Sep-18 17 12Sep2018 20Sep2018 Table B Dates Sales 05Sep2018 81 06Sep2018 95 07Sep2018 21 08Sep2018 23 09Sep2018 48 10Sep2018 78 11Sep2018 13 12Sep2018 10 13Sep2018 91 14Sep2018 60 15Sep2018 30 16Sep2018 81 17Sep2018 4 18Sep2018 11 19Sep2018 5 20Sep2018 77 I need to join these two tables such that it adds all the sales within the date range with respect to id. If there are multiple entry with id for same date, it needs to be divided equally among the id's. Output table calculation would look like this- id start_dt end_dt sales 15 05Sep2018 12-Sep-18 81+95+21+23+48+78/2+13/3+10/3 15 10Sep2018 20-Sep-18 78/2+13/3+10/3+91+60+30+81+4+11+5+77 15 11Sep2018 12-Sep-18 13/3+10/3 16 05Sep2018 20-Sep-18 81+95+21+23+48+78+13+10+91+60+30+81+4+11+5+77 17 06Sep2018 6-Sep-18 95 17 12Sep2018 20Sep2018 10+91+60+30+81+4+11+5+77 and the output table should look like this- id start_dt end_dt sales 15 05Sep2018 12-Sep-18 314.6667 15 10Sep2018 20-Sep-18 405.6667 15 11Sep2018 12-Sep-18 7.666667 16 05Sep2018 20-Sep-18 728 17 06Sep2018 6-Sep-18 95 17 12Sep2018 20Sep2018 369 Any help is highly appreciated and would love to clarify if you have any questions. Thank you.

hvempati · ‎08-01-2018

Hi @Tom and @PeterClemmensen, Do you think there would be a performance issue by using a method like this? I was using a method where I was manually inputting all the dates. But now by using this method, I have seen the runtime for the same code was slow. (previously the code ran for 3 minutes, but now it runs for 10 minutes.) I haven't changed anything except this. Any inputs on this? Thank you.

hvempati · ‎07-28-2018

Can you edit the format as '2018-07-02' (yy-mm--dd) in the answer. Thank you so much..!!

hvempati · ‎07-28-2018

Thank you @PeterClemmensen for the answer. While I appreciate your answer, will those variables in Data be converted into Macro Variable? If not, I give these variables in one program in Enterprise guide and use it in different programmes as well. Can these values be transferred to different programmes as well? Thank you.

hvempati · ‎07-28-2018

I am creating a macro variable for dates such that %let change_date= '2018-07-01' %put &change_date %let interval = 15 (or 30 or 45) and it should give me a macro variable with dates %let previous_end_date = '2018-06-30' (This is change_date -1) %let previous_start_date= '2018-06-16' (this is change_date -15) %let current_start_date= '2018-07-02' (This is change_date+1) %let current_end_date= '2018-07-16' (This is change_date+15) I want the dates to be in this format as this is how Teradata accept the date values. Can you help me out on how to do this macro's calculation? Any help is highly appreciated.

hvempati · ‎07-17-2018

Hi, @art297 Thank you for your reply. I had figured out that the input data I was giving was incorrect.

hvempati · ‎07-11-2018

I have two tables A and B. Table A has Id, Item_Name, price_start_date, new_price_end_date Table B has Covers, Date. I want to create a code to add all the covers in Table A which lie in between Price_start_date and new_price_end_date (that is sum of covers between the dates in Table B). I wrote a code to Proc SQL; create table work.covers_before_total as Select a.*, Sum(Case When a.Price_start_Date <= a.NEW_Price_End_Date and a.Price_start_Date <= b.date and a.NEW_Price_End_Date >= b.date then b.Covers Else 0 END) as covers from work.covers_before a,work.covers_measurement_model b group by a.id Run; But the sum gives out 0 for some reason. Is there also a better way of doing it?

Online Status	Offline
Date Last Visited	‎03-22-2019 09:21 PM

Re: Difference between any two dates greater then 30

Re: Difference between any two dates greater then 30

Re: Difference between any two dates greater then 30

Difference between any two dates greater then 30

How to change the length of Num variable

Re: Joining two tables with date ranges

Joining two tables with date ranges

Re: SAS Date variable macro

Re: SAS Date variable macro

Re: SAS Date variable macro

Re: Difference between any two dates greater then 30

Re: How to change the length of Num variable

Re: SAS Date variable macro

Re: SAS Date variable macro

macro variable calculation

Re: Difference between any two dates greater then 30

Re: Difference between any two dates greater then 30

Re: Difference between any two dates greater then 30

Difference between any two dates greater then 30

How to change the length of Num variable

Re: Joining two tables with date ranges

Joining two tables with date ranges

Re: SAS Date variable macro

Re: SAS Date variable macro

Re: SAS Date variable macro

SAS Date variable macro

Re: Sumif equavalent in SAS

Sumif equavalent in SAS