cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
t34
‎07-22-2021
  • 25 Posts
  • 18 Likes Given
  • 0 Solutions
  • 0 Likes Received

Re: Change a code because the Data Base is so big and doesn´t work

by Obsidian | Level 7 in SAS Programming
‎07-09-2021 05:25 AM
‎07-09-2021 05:25 AM
thanks a lot  @Patrick !!! The table that I want is tabla_cliente with the variable estado12m_cliente. I mean this one: PERIODO NUP fecha estado_cliente estado12m_cliente ... View more

Re: Change a code because the Data Base is so big and doesn´t work

by Obsidian | Level 7 in SAS Programming
‎07-09-2021 05:17 AM
‎07-09-2021 05:17 AM
@ballardw  Please, below the inputs: data tabla_clientes_; infile datalines delimiter=','; input Periodo:8. NUP:4. FECHA:3. Estado_cliente:1.; datalines; 201101,17,1,2 201101,225,1,1 201101,911,1,5 201101,2004,1,4 201102,17,2,1 201102,225,2,1 201102,911,2,5 201102,2004,2,4 201103,17,3,1 201103,225,3,1 201103,911,3,5 201103,2004,3,4 201104,17,4,1 201104,225,4,1 201104,911,4,5 201105,17,5,1 201105,225,5,1 201105,911,5,5 201106,17,6,1 201106,225,6,1 201107,17,7,1 201107,225,7,1 201108,17,8,1 201108,225,8,1 201109,17,9,1 201109,225,9,1 201110,17,10,1 201110,225,10,1 201111,17,11,1 201111,225,11,1 201112,17,12,1 201112,225,12,1 201201,17,13,1 201201,225,13,1 201202,17,14,2 201202,225,14,1 201203,17,15,1 201203,225,15,1 201204,17,16,1 201204,225,16,1 201205,17,17,1 201205,225,17,1 201206,17,18,1 201206,225,18,1 201207,17,19,1 201207,225,19,1 201208,17,20,1 201208,225,20,1 201209,17,21,1 201209,225,21,1 201210,17,22,1 201210,225,22,1 201211,17,23,1 201211,225,23,1 201212,17,24,1 201212,225,24,1 201301,17,25,. 201301,225,25,1 201302,17,26,1 201302,225,26,1 201303,17,27,1 201303,225,27,1 201304,17,28,1 201304,225,28,1 201305,17,29,. 201305,225,29,1 201306,17,30,. 201306,225,30,1 201307,17,31,1 201307,225,31,1 201308,17,32,1 201308,225,32,1 201309,17,33,1 201309,225,33,1 201310,17,34,1 201310,225,34,1 201311,17,35,1 201311,225,35,1 201312,17,36,1 201312,225,36,1 201401,17,37,1 201401,225,37,1 201402,17,38,1 201402,225,38,1 201403,17,39,1 201403,225,39,1 201404,17,40,1 201404,225,40,1 201405,17,41,. 201405,225,41,1 201406,17,42,. 201406,225,42,1 201407,17,43,. 201407,225,43,1 201408,17,44,. 201408,225,44,1 201409,17,45,. 201409,225,45,1 201410,17,46,. 201410,225,46,1 201411,17,47,. 201411,225,47,1 201411,2004,47,1 201412,17,48,. 201412,225,48,1 201412,2004,48,1 201501,17,49,. 201501,225,49,1 201501,2004,49,1 201502,17,50,. 201502,225,50,1 201502,2004,50,1 201503,17,51,. 201503,225,51,1 201503,2004,51,1 201504,17,52,. 201504,225,52,1 201504,2004,52,1 201505,17,53,. 201505,225,53,1 201505,2004,53,1 201506,17,54,. 201506,225,54,1 201506,2004,54,1 201507,17,55,. 201507,225,55,1 201507,2004,55,1 201508,17,56,. 201508,225,56,1 201508,2004,56,1 201509,17,57,. 201509,225,57,1 201509,2004,57,1 201510,17,58,. 201510,225,58,1 201510,2004,58,1 201511,17,59,. 201511,225,59,1 201511,2004,59,1 201512,17,60,. 201512,225,60,1 201512,2004,60,1 201601,17,61,. 201601,225,61,1 201601,2004,61,1 201602,17,62,. 201602,225,62,1 201602,2004,62,1 201603,17,63,. 201603,225,63,1 201603,2004,63,1 201604,17,64,. 201604,225,64,1 201604,2004,64,1 201605,17,65,. 201605,225,65,1 201605,2004,65,1 201606,17,66,. 201606,225,66,1 201606,2004,66,1 201607,17,67,. 201607,225,67,1 201607,2004,67,1 201608,17,68,. 201608,225,68,1 201608,2004,68,1 201609,17,69,. 201609,225,69,1 201609,2004,69,1 201610,17,70,. 201610,225,70,1 201610,2004,70,1 201611,17,71,. 201611,225,71,1 201611,2004,71,1 201612,17,72,. 201612,225,72,1 201612,2004,72,1 201701,17,73,. 201701,225,73,1 201701,2004,73,1 201702,17,74,. 201702,225,74,1 201702,2004,74,1 201703,17,75,. 201703,225,75,1 201703,2004,75,1 201704,17,76,. 201704,225,76,1 201704,2004,76,1 201705,17,77,. 201705,225,77,1 201705,2004,77,1 201706,17,78,. 201706,225,78,1 201706,2004,78,1 201707,17,79,. 201707,225,79,1 201707,2004,79,1 201708,17,80,. 201708,225,80,1 201708,911,80,1 201708,2004,80,1 201709,17,81,. 201709,225,81,1 201709,911,81,1 201709,2004,81,1 201710,17,82,. 201710,225,82,1 201710,911,82,1 201710,2004,82,1 201711,17,83,. 201711,225,83,1 201711,911,83,1 201711,2004,83,1 201712,17,84,. 201712,225,84,1 201712,911,84,2 201712,2004,84,1 201801,17,85,. 201801,225,85,1 201801,911,85,2 201801,2004,85,1 201802,17,86,. 201802,225,86,1 201802,911,86,3 201802,2004,86,1 201803,17,87,. 201803,225,87,1 201803,911,87,3 201803,2004,87,1 201804,17,88,. 201804,225,88,1 201804,911,88,3 201804,2004,88,1 201805,17,89,. 201805,225,89,1 201805,911,89,4 201805,2004,89,1 201806,17,90,. 201806,225,90,1 201806,911,90,4 201806,2004,90,1 201807,17,91,. 201807,225,91,1 201807,911,91,4 201807,2004,91,1 201808,17,92,. 201808,225,92,1 201808,911,92,4 201808,2004,92,1 201809,17,93,. 201809,225,93,1 201809,911,93,4 201809,2004,93,1 201810,17,94,. 201810,225,94,1 201810,911,94,4 201810,2004,94,1 201811,17,95,. 201811,225,95,1 201811,2004,95,1 201812,17,96,. 201812,225,96,1 201812,2004,96,1 201901,17,97,. 201901,225,97,1 201901,2004,97,1 201902,17,98,. 201902,225,98,1 201902,2004,98,1 201903,17,99,. 201903,225,99,1 201903,2004,99,1 201904,17,100,. 201904,225,100,1 201904,2004,100,1 201905,17,101,. 201905,225,101,1 201905,2004,101,1 201906,17,102,. 201906,225,102,1 201906,2004,102,1 201907,17,103,. 201907,225,103,1 201907,2004,103,1 201908,17,104,. 201908,225,104,1 201908,2004,104,1 201909,17,105,. 201909,225,105,1 201909,2004,105,1 201910,17,106,. 201910,225,106,1 201910,2004,106,1 201911,17,107,. 201911,225,107,1 201911,2004,107,1 201912,17,108,. 201912,225,108,1 201912,2004,108,1 202001,17,109,. 202001,225,109,1 202001,2004,109,1 202002,17,110,. 202002,225,110,1 202002,2004,110,1 202003,17,111,. 202003,225,111,1 202003,2004,111,1 ;; data default_contratos; infile datalines delimiter=','; input NUP:4. t:3. Estado_cliente:1.; datalines; 911,1,5 2004,1,4 911,2,5 2004,2,4 911,3,5 2004,3,4 911,4,5 911,5,5 911,86,3 911,87,3 911,88,3 911,89,4 911,90,4 911,91,4 911,92,4 911,93,4 911,94,4 ;; The period of date are from 201101 to 202003. The relation between period and (t=fecha) 201101 --> t=1 201401 --> t=37 202003 --> t=111 ... View more

Re: Change a code because the Data Base is so big and doesn´t work

by Obsidian | Level 7 in SAS Programming
‎07-09-2021 03:55 AM
‎07-09-2021 03:55 AM
Yes, in the final table. For each row (each date). I need a variable that indicates the variable status of 12 months previous but just the flag of these 12 months if any of them is >=3 then flag 1 ... View more

Re: Change a code because the Data Base is so big and doesn´t work

by Obsidian | Level 7 in SAS Programming
‎07-09-2021 03:28 AM
‎07-09-2021 03:28 AM
Thanks Chris. I don´t know the RAM of the server but it is limited (I have no access of it) For this reason, I mentioned the option to split the tables monthly ... View more

Re: Change a code because the Data Base is so big and doesn´t work

by Obsidian | Level 7 in SAS Programming
‎07-08-2021 11:32 PM
‎07-08-2021 11:32 PM
Thanks ChrisNZ. 1. Can you show me with an example what you are recommending me? Please 2.ok 3 .The table tabla_cliente is the biggest one (700M) and the other is like 400M 4. I don’t know how to use it ... View more

Re: Change a code because the Data Base is so big and doesn´t work

by Obsidian | Level 7 in SAS Programming
‎07-08-2021 02:49 PM
‎07-08-2021 02:49 PM
Thanks for your reply. Please, below my comments: 1) On the table “default_contratos” for each observation date corresponding to a given “fecha” or “t”, I am expected to check in the previous 12 months if estado_cliente gets a value >=3 (in any of the last 12 months) then status12=1 else status12=0   2) I need to join “default_contratos” table with “table_cliente” by NUP (id_cliente) and by date (period or t) The purpose of this union is get the estado12m_cliente variable: If estado_cliente >=3 then estado12m_cliente=2; Else if status12=1 and estado_cliente<3 then estado12m_cliente=1; Else if status12=0 and estado_cliente<3 then estado12m_cliente=0;     Also note: Periodo is the date (numeric) T=Fecha is the date (numeric). The same date but the start is 1 and so on.. Estado_cliente is the variable to work (numeric) NUP: is the ID (numeric) The objetive of this is to deal with 700M of records in an effecient way Please, find the input attach in excel ... View more

Change a code because the Data Base is so big and doesn´t work

by Obsidian | Level 7 in SAS Programming
‎07-08-2021 01:27 PM
‎07-08-2021 01:27 PM
Hello SAS world, I have a code which works only with small table but I need to create monthly to use in a big data base (need to change the code but I didn´t get it) The idea is: 1) On the table “default_contratos” for each observation date corresponding to a given “fecha” or “t”, I am expected to check in the previous 12 months if estado_cliente gets a value >=3 (in any of the last 12 months) then status12=1 else status12=0   2) I need to join “default_contratos” table with “table_cliente” by NUP (id_cliente) and by date (period or t) The purpose of this union is get the estado12m_cliente variable: If estado_cliente >=3 then estado12m_cliente=2; Else if status12=1 and estado_cliente<3 then estado12m_cliente=1; Else if status12=0 and estado_cliente<3 then estado12m_cliente=0;     Also note: Periodo is the date (numeric) T=Fecha is the date (numeric). The same date but the start is 1 and so on.. Estado_cliente is the variable to work (numeric) NUP: is the ID (numeric) The objetive of this is to deal with 700M of records in an effecient way Input tables attached in response below (xlsx) /*Previous code*/ %macro default; %do i = 1 %to 12; data pd.default_contratos_&i (rename=estado_cliente=estado&i drop = t compress = yes); set pd.default_contratos /*(obs=10)*/; fecha=t-&i; run; proc sql; create index nupfec on pd.default_contratos_&i(nup,fecha); quit; data pd.tabla_cliente_ (compress=yes); set output.tabla_cliente_; set pd.default_contratos_&i (keep= nup fecha estado&i) key = nupfec / unique; if _iorc_ > 0 then do; _ERROR_ = 0; call missing(estado&i); end; run; ; %end; %mend default; %default; data output.tabla_estado12m (drop= estado1-estado12 compress = yes); set pd.tabla_cliente_; if estado_cliente >= 3 then estado12m_cliente = 2; if estado_cliente < 3 then do; if estado1 >= 3 or estado2 >= 3 or estado3 >= 3 or estado4 >= 3 or estado5 >= 3 or estado6 >= 3 or estado7 >= 3 or estado8 >= 3 or estado9 >= 3 or estado10 >= 3 or estado11 >= 3 or estado12 >= 3 then estado12m_cliente = 1; else estado12m_cliente = 0; end; run;   ... View more

Re: Create a counter that depends by amount

by Obsidian | Level 7 in SAS Programming
‎01-27-2021 07:40 AM
‎01-27-2021 07:40 AM
Thanks! Count_debit should increment when the debit amount is not missing. I need to link count_debit with the amount. If there is a debit of 100, I need to count=1 in the counter_debit_100 but if there is a debit of 635 , I cannot count for 100, I need to count for 635. For example: Debit= 100 --> count_debit=1, amount=100 Debit= 100 --> count_debit=2, amount=100 Debit= 635 --> count_debit=1, amount=635   ... View more

Create a counter that depends by amount

by Obsidian | Level 7 in SAS Programming
‎01-27-2021 07:21 AM
‎01-27-2021 07:21 AM
Hello everyone,   I need to create a counter that has to count if is the same agreement, same day and it depends if is credit or debit and the amount of debit or credit. They have to be dynamic because I don´t know times that there is a different amount on the same day.So, It may be a solution is with arrays... but I don´t know how to do it.   Below, you can find the counters that I need: Count credit and Count debit. Agreement Effective_date1 Debit Credit Count credit Count debit Amount a1 01/02/2020 100   0 1 100 a1 01/02/2020 632   0 1 632 a1 01/02/2020   100 1 1 100 a1 01/02/2020   632 1 1 632 a1 01/02/2020 100   1 2 100 a1 01/02/2020 632   1 2 632 a1 01/02/2020   3 1 0 3 a1 01/02/2020 3   0 1 3 a1 22/02/2020 3   0 1 3 a2 02/03/2020 50   0 1 50   data have; infile datalines delimiter=','; informat Agreement $char4. Effective_Date1 ddmmyy10. Debit Credit; format Agreement $char4. Effective_Date1 ddmmyy10. Debit Credit; input Agreement Effective_Date1 Debit Credit; datalines; A1,01/02/2020,100,0 A1,01/02/2020,632,0 A1,01/02/2020,0, 100 A1,01/02/2020,0,632 A1,01/02/2020,100,0 A1,01/02/2020,632,0 A1,01/02/2020,0,3 A1,01/02/2020,3,0 A1,22/02/2020,3,0 A2,02/03/2020,50,0 ;;   Thanks a lot! Regards, ... View more

Create flag to connect records

by Obsidian | Level 7 in SAS Programming
‎01-25-2021 06:18 PM
‎01-25-2021 06:18 PM
Hi,    I need to create a flag to identify records. The aim is to avoid transactions that credit=debit on the same day. It´s a bit tricky because I have a count of days and I try to connect to the 2nd debit the credit, to avoid mistakes of count of days. Agreement Effective_Date1 Debit Credit Flag A1 10/09/2019 0 30   A1 11/09/2019 0 750   A1 11/09/2019 0 750   A1 11/09/2019 0 260,75 1 A1 11/09/2019 260,75 0 1 A1 11/09/2019 260,75 0   A1 11/09/2019 260,75 0   A1 11/09/2019 260,75 0   A2 04/08/2020 0 1   A2 11/08/2020 150 0   A2 11/08/2020 150 0 2 A2 11/08/2020 150 0 3 A2 11/08/2020 0 150 2 A2 11/08/2020 0 150 3 A3 12/09/2020 100 0   A3 21/09/2020 100 0 5 A3 21/09/2020 100 0 4 A3 21/09/2020 0 100 4 A3 21/09/2020 0 100 5 A3 21/09/2020 0 100     Thanks in advance! ... View more

Re: Create net columns by day and customer - arrays

by Obsidian | Level 7 in SAS Programming
‎11-25-2020 04:23 PM
‎11-25-2020 04:23 PM
I mean "same day" when a record has got the equal Effective_date1 than other record ... View more

Re: Create net columns by day and customer - arrays

by Obsidian | Level 7 in SAS Programming
‎11-25-2020 01:21 PM
‎11-25-2020 01:21 PM
Sorry @ballardw  For each customer and effective_date  NetCredit= sum of values of credits of the same day and effective day NetDebit= sum of values of debits of the same day and effective day NetAmountArrears= sum of values of amount arrearsof the same day and effective day When there are records in the same day, all net columns have to value the same in all records of these days.   If effective_date are differents for each customer amount_arrears = last_amount_arrears + cr - dr   You can forget about balance (it is not interested here) Thanks a lot ... View more

Re: Create net columns by day and customer - arrays

by Obsidian | Level 7 in SAS Programming
‎11-25-2020 12:51 PM
‎11-25-2020 12:51 PM
First,apologies for the explanation A customer can have several agreeements I need the net value for each day by agreement in every row. Os in your example (without date) , I need the value 459 for every A and 66 for every B ... View more

Re: Create net columns by day and customer - arrays

by Obsidian | Level 7 in SAS Programming
‎11-25-2020 11:54 AM
‎11-25-2020 11:54 AM
Thanks a lot for your answer!!! The problem is that in the real table- on same day, there are 3, 4 or... n transactions ... View more

Re: Create net columns by day and customer - arrays

by Obsidian | Level 7 in SAS Programming
‎11-25-2020 11:50 AM
‎11-25-2020 11:50 AM
Sum the values of each column for same customer and same day ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎07-22-2021 05:47 PM
Likes given to
Quick links