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

[t34]

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;

[ballardw]

Suggestion:

Provide small example data set with only the variables used for the start of this.

Describe the rules.

Provide an example of the output for the example data set.

I suspect this can likely be done without any macro programming but need to see an actual example.

[t34]

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

[ballardw]

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same name, type (numeric or character) and even values.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

An example of the desired result for that input is a good idea as well.

Previous to what? The current record? A specific date you supply? Do your records represent one month per Id or are there multiple records in a given calendar month? If there is no record "n months previous" what is done with all the N month indicated variables?

From the "date" examples in your code then the "numeric" nature is really pain to work with when crossing year boundaries, which is why creating SAS date values will likely improve anything related to the comparisons.

[t34]

@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

[Tom]

Why are you storing 01JAN2011 as the number 201,101 instead of as an actual date?

It looks like T is just the count of the number of months since 01JAN2011, counting from 1.

Also if you are worried about the size of the dataset you do not need to store both PERIOD and T as one can easily be derived from the other using with INTCK() or INTNX() function. 

For programming the integer T will work better than the pseudo date digits.  But let's use FECHA instead of T as the name of this variable so that the name imparts a little more explanation of what the variable means.

So your date range is the 111 months starting with 01JAN2011, but some of your FUP values (clients?) are missing some of the months.  It will work easier to fill in those gaps.  Given that you know the ranges of FECHA is from 1 to 111 you can do that pretty easily with a data step view.

First thing is to sort your input dataset with the monthly status by FUP and FECHA.  Then you can make a skeleton dataset that has all FUP*FECHA combinations like this:

data skeleton / view=skeleton;
  set tabla_clientes_;
  by nup;
  if first.nup then do fecha=1 to 111;
    output;
  end;
  keep nup fecha;
run;

And you can then merge that back with the original and the result will have 111 observations per FUP value.

Now you can use a temporary array of length 12 to store the last 12 months results of your conditional test so that you can add your new 12 months flag variable.

data full ;
  merge skeleton tabla_clientes_;
  by nup fecha;
  array year [0:11] _temporary_;
  if first.nup then call missing( of year[*]);
  year[mod(fecha,12)]= (Estado_cliente>=3);
  status_12 = 1=max(of year[*]);
run;

But it is not clear to me what you want to do with this new flag variable now that you have it.

[ChrisNZ]

1. This step

data pd.default_contratos_&i (rename=estado_cliente=estado&i drop = t compress = yes);
set pd.default_contratos /*(obs=10)*/;
fecha=t-&i;
run;

is unnecessary.
Why not do the date loop when creating the the TABLA_CLIENTE table, rather than matching the same data 12 times?

2. The data set TABLA_CLIENTE is overwritten in the macro

3. Which table has 700m records?

4. This looks like the perfect use case for a hash table. Have you tried that?

[t34]

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

[ChrisNZ]

A 400m-row hash table with one numeric key and 2 numeric data variables requires over 24 GB of RAM. Can you access 32 GB?

[t34]

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

[ChrisNZ]

> 2.ok

So what do you need? All 12 runs in the same final table?

[t34]

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

[Patrick · Accepted solution]

@t34 With the volumes you are dealing with having some knowledge about the environment (like how much memory available) and source tables (like: are the already sorted, are there indexes on the tables) becomes necessary.

Based on the sample data you've provided it looks like that only a minority of rows in tabla_cliente will satisfy a criterion of estado_cliente>=3. 

For a hash approach this is the table you need to load into memory and though if you can filter out a majority of rows prior to loading then memory requirements might just be below of what you've got available.

Test with your sample data if below code returns the expected result - and if it does then run the logic against your full data volume and see if you run into an out-of-memory condition or not. 

options memsize=0; tells SAS to use as much memory as it needs (until there is no further memory available).

options memsize=0;
data default_contratos;
  input NUP t estado_cliente;
  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
;

data tabla_clientes;
  input PERIODO:yymmn6. NUP fecha estado_cliente;
  format periodo yymmn6.;
/*  if nup=911 then 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
;

proc sql;
  create view v_tabla_clientes as 
    select
      nup,
      fecha
    from tabla_clientes
    where estado_cliente>=3
    ;
quit;

data want(compress=yes);

  if _n_=1 then
    do;
      dcl hash h1(dataset:'v_tabla_clientes');
      h1.defineKey('nup','fecha');
      h1.defineData('nup');
      h1.defineDone();
    end;

  set default_contratos;

  /* check previous 12 months */
  estado12m_cliente=0;
  do fecha=t-1 to t-13 by -1;
    if h1.check()=0 then 
      do;
        estado12m_cliente=1;
        leave;
      end;
  end;

  drop fecha;

run;

[t34]

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

[Patrick]

---

@t34 wrote:

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

---

@t34 Then what's the purpose of default_contratos? Why can't you just use tabla_clientes with entries per month and though you just check for any given row in tabla_clientes if you find a "matching" row in the previous 12 months where estado_cliente>=3 ?

Also: Is your real tabla_clientes table already sorted by PERIODO like your sample data suggests?