About Tal

Tal · ‎01-20-2013

12/23/2012 opening balance=5,closing balance=5-1=4 12/24/2012 opening blance =4 closing balance=(4+2+3-4-5)=0 12/25/2012 opening balance=0,closing balance=0+6-7=-1 (sorry made a typo in the previous post the top closing balance should read 4 not 1. So the formulas seem right in the code but still dont get the right values

Tal · ‎01-20-2013

your code seems right but still dont know why it s not giving me the right values for the closing balance this is the dataset: Calendar_Day Status Sum 12/23/2012 Terminated 1 12/24/2012 Restarted 2 12/24/2012 Start 3 12/24/2012 Suspension 4 12/24/2012 Terminated 5 12/25/2012 Restarted 6 12/25/2012 Terminated 7 and this is what i am supposed to get date opening closing 12/23/2012 5 1 12/24/2012 4 0 12/25/2012 0 -1

Tal · ‎01-19-2013

in other words opening_balance=5 is only for 12/23/2012 then the opening balance for next day would be whatever the closing one for the current day is and so on

Tal · ‎01-19-2013

yes Arthur,i need something as your code is giving me except that the opening balance is the closing balance of the previous day.I tried the lag function but not working for me.I am doing it wrong Thanks a lot for your code

Tal · ‎01-19-2013

hey Arthur, thanks for your response but no this is not what i need to achieve. I actually need to group the data by calendar day and find the closing and opening balance for each group not for each single transaction as your code does .So for 12/24/2012 i have opening balance 5 and the closing balance should be 5+2+3-4-5=4.

Tal · ‎01-18-2013

hi guys, I need your help here please i have this dataset (mylib.active_suscribers_grouped) below and i need to add two more fields to it (opening and closing balance ) grouped by calendar day.The closing balance =opening balance+Start+Restarted-Suspension-Termination and the opening balance is the closing balance from the previous day.The opening balance for 12/23/2012 is given to me. Calendar_Day Status Sum 12/23/2012 Terminated 1 12/24/2012 Restarted 2 12/24/2012 Start 3 12/24/2012 Suspension 4 12/24/2012 Terminated 5 12/25/2012 Restarted 6 12/25/2012 Terminated 7 so i need to get something like this: Calendar_Day Status Sum opening_balance closing_balance 12/23/2012 Terminated 1 5 4 12/24/2012 Restarted 2 4 0 12/24/2012 Start 3 4 0 12/24/2012 Suspension 4 4 0 12/24/2012 Terminated 5 4 0 12/25/2012 Restarted 6 0 -1 12/25/2012 Terminated 7 0 -1 and i am using this code below but i am not gettin what i am supposed to.Where i am wrong,please? data mylib.active_suscribers_summed; do _n_ = 1 by 1 until(last.calendar_day); set mylib.active_suscribers_grouped; by calendar_day; if _n_=1 then do; opening_balance=5; if status='Restarted' or status='Start' then Closing_balance=sum(opening_balance,Sum); else Closing_balance=sum(opening_balance,-Sum); end; if _n_ ne 1 then do; opening_balance=lag(closing_balance); if status='Restarted' or status='Start' then Closing_balance=sum(Opening_Balance,Sum); else Closing_balance=sum(Opening_balance,-Sum);end;end; do _n_ = 1 to _n_ ; set mylib.active_suscribers_grouped; output ; end ; run; the only thing i am getting right is the opening/closing balance for 12/23/2012 Thanks

Tal · ‎01-14-2013

i mean i am supposed to check whether the # of newuser(free of charge or charge) for 13/12/12 would be less or more then the # of same type on 12/12/12

Tal · ‎01-14-2013

hey Cynthia,here is some sample of the data date newspaper subscr. item_type cost; 12/12/12/ nytimes newuser free_of charge 0; 12/12/12/ nytimes opt-out paid $2 12/12/12/ toronto_star restarter paid $2.5 ...................... ....................... lets say desired output-excel.I could use formats if i get more types of subscribers who would not bring good revenue in total.What do you mean change the data? There wont be data to compare the first daily report with.The second daily report should be compared with the first.The third daily report gets compared with the first two and so on.. Do i make any sensen ?

Tal · ‎01-14-2013

hi guys, i need your thoughts/ideas..... on this please i work for a news company and they are implementing SAS.I would be asked to do daily reports on how the sales goes each day of the week.The dataset would have 2-3 news papers with the type of subscription (new subscribers,returning users,users who would cancel.. and each subscription would be either with a fee or free of charge and with the counts for each of them.I would also need to compare the results(counts) of the current day with the ones from the last 7 days prior to the current date.I am thinking proc tabulate where row dimension would be the three news papers and the column dimension being the different subscriptions broken down to (charge or free of charge ).But then how would i compare the counts of the current with the ones from the last 7 days prior to it? Maybe having the days of the week as page dimensions? Any thoughts please ?

Tal · ‎01-13-2013

Thanks Tom

Tal · ‎01-13-2013

i want to add another question to this if let's say i need to calculate the total sum of sum(x) and sum (y) of the table below can i achieve it with the same query or do i need to run another one? 12-2-2012 10 12 12-5-2012 7 11 12-3-2012 2 2 total: 19 25

Tal · ‎01-12-2013

actually Karl,i was running this queries on sql developer /oracle and the "create table as ..." worked perfectly for me.Probably the issue was with sum(x),sum(y.I was forgetting the sum(x) as... or sum (y) as.. So thanks guys,thank you all

Tal · ‎01-10-2013

never mind the last question.I must have ran the first query without the "table" from "from table A" otherwise it wont run but i still cannot run the second query

Tal · ‎01-10-2013

i mean i have "table" in my own query too but i was wondering if it s necessary

Tal · ‎01-10-2013

thanks for the quick response Linlin but i did run the same code w/o the brackets after "as" and before ; and i still get the error.Is "table" from "from table A" really necessary ?

Online Status	Offline
Date Last Visited	‎06-01-2024 03:49 PM

Re: 6 months back

6 months back

Re: Copying files from the SAS server to a NAS folder (sharedrive)

Copying files from the SAS server to a NAS folder (sharedrive)

Re: Parsing a JSON File

Parsing a JSON File

Re: Parsing JSON data

Re: Parsing JSON data

Re: Parsing JSON data

Re: Parsing JSON data

Re: Parsing JSON data

Re: Parsing JSON data

Re: Parsing JSON data

Re: Parsing JSON data

Re: Parsing JSON data

macro question

Re: Data Step

Re: How not to loose observations in PROC TABULATE when having missin...

Re: date format

Re: Four if statements in one data step

Re: dow loops

Re: dow loops

Re: dow loops

Re: dow loops

Re: dow loops

dow loops

Re: proc tabulate a good choice?

Re: proc tabulate a good choice?

proc tabulate a good choice?

Re: SQL code

Re: SQL code

Re: SQL code

Re: SQL code

Re: SQL code

Re: SQL code