SergioSanchez Tracker

Re: Is there any solution for this?

SergioSanchez — Mon, 22 Sep 2014 16:17:08 GMT

Astounding, I'll keep in mind your advices .

Thanks

Re: Is there any solution for this?

SergioSanchez — Fri, 19 Sep 2014 12:32:30 GMT

Hi all

Thank you very much for all your help. it's much appreciated for me.

Regards

Re: Is there any solution for this?

SergioSanchez — Fri, 19 Sep 2014 08:08:13 GMT

Morning all.

I attached one screenshot of my dataset. The key is only var1 (numeric, 8 length) . It's something like a social Security but with less length. I need almost all field because I need to check if this variables match it with other variables that there are in the source.

Thanks

Re: Is there any solution for this?

SergioSanchez — Thu, 18 Sep 2014 11:53:23 GMT

Thanks all for your ideas to improve the code. All code related to deal with memory system simply don't work due to limitations of size (only 4 gb) and the impossibility to change it fo a new powerfull one.

I'd try splitting in more datasets (perhaps 20 or 25) but how is the bes option for merge all those datasets?.

Could I make an array in the merge statement?

Regards

Re: Is there any solution for this?

SergioSanchez — Wed, 17 Sep 2014 13:48:00 GMT

Thanks all for your comments

I have made one text splitting the dataset in 10 smaller datasets, the results given are below

options fullstimer;

proc sort data = Dataset_A out = Dataset_B (compress=binary);

by KEY;

run;

NOTE: There were 32498768 observations read from the data set Dataset_A.

NOTE: The data set WORK.PERSONSHORT has 32498768 observations and 31 variables.

NOTE: Compressing data set Dataset_B decreased size by 71.08 percent.

Compressed is 391645 pages; un-compressed would require 1354116 pages.

NOTE: PROCEDURE SORT used (Total process time):

real time 44:04.28

user cpu time 1:54.30

system cpu time 1:13.66

Memory 66929k

OS Memory 100216k

Timestamp 16/09/2014 17:22:45

Now I splitt the dataset with a macro (thanks to Selvaratnam Sridharma for the tool )

data _null_;

33 if 0 then set ec_dim_per nobs=count;

34 call symput('numobs',put(count,8.));

35 run;

36 %let n=%sysevalf(&numobs/&num,ceil);

37 data %do J=1 %to &num ; DATASET_B_&J %end; ;

38 set DATASET_B;

39 %do I=1 %to &num;

40 if %eval(&n*(&i-1)) <_n_ <= %eval(&n*&I)

41 then output DATASET_B_&I;

42 %end;

43 run;

44 %mend split;

45 %split (10)

NOTE: DATA STEP stopped due to looping.

NOTE: DATA statement used (Total process time):

real time 0.04 seconds

cpu time 0.00 seconds

NOTE: There were 32498768 observations read from the data set WORK.DATASET_B.

NOTE: The data set WORK.DATASET_B_1 has 3249877 observations and 31 variables.

NOTE: The data set WORK.DATASET_B_2 has 3249877 observations and 31 variables.

NOTE: The data set WORK.DATASET_B_3 has 3249877 observations and 31 variables.

NOTE: The data set WORK.DATASET_B_4 has 3249877 observations and 31 variables.

NOTE: The data set WORK.DATASET_B_5 has 3249877 observations and 31 variables.

NOTE: The data set WORK.DATASET_B_6 has 3249877 observations and 31 variables.

NOTE: The data set WORK.DATASET_B_7 has 3249877 observations and 31 variables.

NOTE: The data set WORK.DATASET_B_8 has 3249877 observations and 31 variables.

NOTE: The data set WORK.DATASET_B_9 has 3249877 observations and 31 variables.

NOTE: The data set WORK.DATASET_B_10 has 3249875 observations and 31 variables.

NOTE: DATA statement used (Total process time):

real time 15:13.22

cpu time 49.68 seconds

NOTE: This SAS session is using a registry in WORK. All changes will be lost at the end of

this session.

Another macro (this time made by me ) to run all the proc sort throught the datasets

46 %macro sort;

47 %do i=1 %to 10;

48 proc sort data = DATASET_B_&i;

49 by KEY;

50 run;

51 %end;

52 run;

53 %mend sort;

54 %short

NOTE: There were 3249877 observations read from the data set WORK.DATASET_B_1.

NOTE: The data set WORK.DATASET_B_1 has 3249877 observations and 31 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 4:19.91

cpu time 17.59 seconds

NOTE: There were 3249877 observations read from the data set WORK.DATASET_B_2.

NOTE: The data set WORK.DATASET_B_2 has 3249877 observations and 31 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 5:08.46

cpu time 19.56 seconds

NOTE: There were 3249877 observations read from the data set WORK.DATASET_B_3.

NOTE: The data set WORK.DATASET_B_3 has 3249877 observations and 31 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 2:29.01

cpu time 19.51 seconds

NOTE: There were 3249877 observations read from the data set WORK.DATASET_B_4.

NOTE: The data set WORK.DATASET_B_4 has 3249877 observations and 31 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 4:03.43

cpu time 17.03 seconds

NOTE: There were 3249877 observations read from the data set WORK.DATASET_B_5.

NOTE: The data set WORK.DATASET_B_5 has 3249877 observations and 31 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 6:03.90

cpu time 18.95 seconds

NOTE: There were 3249877 observations read from the data set WORK.DATASET_B_6.

NOTE: The data set WORK.DATASET_B_6 has 3249877 observations and 31 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 6:44.73

cpu time 19.23 seconds

NOTE: There were 3249877 observations read from the data set WORK.DATASET_B_7.

NOTE: The data set WORK.DATASET_B_7 has 3249877 observations and 31 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 2:48.26

cpu time 18.72 seconds

NOTE: There were 3249877 observations read from the data set WORK.DATASET_B_8.

NOTE: The data set WORK.DATASET_B_8 has 3249877 observations and 31 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 3:36.37

cpu time 20.17 seconds

NOTE: There were 3249877 observations read from the data set WORK.DATASET_B_9.

NOTE: The data set WORK.DATASET_B_9 has 3249877 observations and 31 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 4:41.83

cpu time 19.00 seconds

NOTE: There were 3249875 observations read from the data set WORK.DATASET_B_10.

NOTE: The data set WORK.DATASET_B_10 has 3249875 observations and 31 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 3:17.87

cpu time 19.48 seconds

the sum of the all sorts procedures are almost 41 min without counting the 15 min for the macro to work. And we still need a merge to build the dataset again (by the way, what do you think is the best way to merge all the datasets?)

As you can see split in 10 dataset isn't faster than the original proc sort. If I able to free space I'd try with perhaps 20 small datasets.

AmySwinford, I have SAS/ACCESS ready for use in my PC but I never use it and to be honest I don´t know where to start to make it work in local mode (server/cliente in the same machine).

I think defragmenting the hard disk is a very good idea , I´d try if my user have admin privileges,...

Regards

Re: Is there any solution for this?

SergioSanchez — Tue, 16 Sep 2014 13:07:03 GMT

Thanks Jaap for this "free of charge lessons" :smileylaugh:.

I`d try to increase the amount of memory in the buffer and total too.

As for your questions, I coul say

- The most of the datases I need the hole set, in case I only need subset it´s already implemented in the code

- Is some dataset there is no order at all, in other the order is not the one I need

- In fact it is not what I was thinking, but it works is doesn´t matter.

What about multiprocessing in local, I'd try to explain myselft. Is posible to run the two short in parallel?. I read about this but I know sure if it could be posible running a local sesion and "emulate" a server on local machine.

Thanks

Re: Is there any solution for this?

SergioSanchez — Tue, 16 Sep 2014 07:54:39 GMT

Good morning

I left here a proc sort, this proc sort is one of the bigger.

72 proc sort data = dataset_1= dataset_2;

73 by key;

74 run;

NOTA: Se han leído 32498768 observaciones del conj. datos WORK.EC_DIM_PER.

NOTA: El conj. datos WORK.PERSORDENADO tiene 32498768 observaciones y 31 variables.

NOTA: PROCEDIMIENTO SORT utilizado (Tiempo de proceso total):

tiempo real 45:25.58

tiempo de cpu del usuario 42.85 segundos

tiempo de cpu del sistema 2:40.40

Memoria 131842k

Jaap, I atttach you a proc sort screenshort, I hope it can resolved your questions.

Ballardw, Hash hasn`t enough memory for run in my PC. It couldn't load the entire table in memory. Please see my post above.

Thanks all for your help

Re: Is there any solution for this?

SergioSanchez — Mon, 15 Sep 2014 16:09:55 GMT

client installation?

Sorry Linush, I don't know what you mean with it.

One of the task of my job is to make sure that the data load from the source is the same that the data that is working with so we take the data set from the source, order it, take the data that is loaded in the system, order it and verify if the data are the same, wich data are in the source and not in the system and viceversa.

My code is something like this below

data a;

set b (where= vardate1<= date1 and vardate2<=date2 and vardate3<=date3 and vardate4<=date4);

run;

data x;

set y (where= vardate1<= date1 and vardate2<=date2 and vardate3<=date3 and vardate4<=date4);

run;

proc sort data = a;

order by key;

proc sort data = x (rename=(var_f = key));

order by key;

data h i j;

merge a (in=w) b(in=q);

if w then output h;

else if g then output i;

else output j;

run;

The problem is that the proc sort takes tooooooooo much time, so I´m trying to replace this piece of the code.

A good alternative was to use a hash object but muy PC hasn't enough memory for perform it with success.

Regards

Re: Is there any solution for this?

SergioSanchez — Mon, 15 Sep 2014 15:03:01 GMT

Thanks guys.

I increase the amount of memory without any result. I need more physical memory installed in my pc, only 4 gb is very short

Only two images to show you the issue.

Before (1.jpg) and after (2.jpg)

Thanks for your help

P:S : A better way to sort a dataset of 40 millions rows instead of Proc report ?

Regards

Is there any solution for this?

SergioSanchez — Mon, 15 Sep 2014 14:01:15 GMT

Hi all

I´m trying to improve my time processing and so I am development a hash object for a faster sort but an error about the amount of memory ocurrs and I not sure if there is any option to avoid it.

Regards

data _null_;

if 0 then set a;

declare hash epi (dataset:"a", ordered:"y");

epi.definekey ("key1","key2");

epi.definedata (all:"yes");

epi.definedone ();

epi.output (dataset: "fastorder");

stop;

run;

ERROR: Hash object added 7864304 items when memory failure occurred.

FATAL: Insufficient memory to execute DATA step program. Aborted during the EXECUTION phase.

ERROR: The SAS System stopped processing this step because of insufficient memory.

NOTE: DATA statement used (Total process time):

real time 9.06 seconds

user cpu time 7.78 seconds

system cpu time 1.20 seconds

Memory 1137687k

OS Memory 1217248k

Timestamp 15/09/2014 15:48:46

Re: Help developing a macro with iterations

SergioSanchez — Fri, 12 Sep 2014 09:10:58 GMT

Tom escribió:

%LOCAL will insure that the macro variables are defined in the current macro's symbol table. So changes made to them will not impact any existing macro variables that were created before the macro started running.

Thanks Tom for share your knowledge with me and thanks damanaulakh88 for your code, it was my first approach to the problem but for some reason I still don't resolved why the counting1-:counting4 are not store like macro variables, in fact they don't appear when you submit %put _all_ statement.

Do you know what could be the reason fo it?

Regards

Re: Help developing a macro with iterations

SergioSanchez — Thu, 11 Sep 2014 09:10:19 GMT

thank

Jaap Karman escribió:

When you have SAS running on several servers you can connect those using SAS/connect.
By doing that you can remote submit SAS-programs between the sas sessions, doing up/down loads and using remote library servers (RLS).

RLS is making the library transparent in a functional way looking at it on the local machine while it on server side.

When you are using SAS/Access to a RDBMS you are also connecting to External data. But in that case is not a SAS session running at the other side.

Thank you so much Jaap

Re: Help developing a macro with iterations

SergioSanchez — Thu, 11 Sep 2014 07:25:16 GMT

Morning all

Thanks Tom, it works fine.

Only a few questions for us .

Sorry Jaap but I don't know what is RLS. Could you light to me please?

Tom, I've never seen before the statement "%local tables i ;". Would you explain to me how to use it?

Thanks

Re: Help developing a macro with iterations

SergioSanchez — Wed, 10 Sep 2014 14:09:26 GMT

Yes, that´s right.

Very good answer, clean and easy, but unfortunately it´s doens't work for me because the datasets are not in my local drive, but if in a remote server so I don't see how many observations are in these datasets

As I said to Jaap in my sashelp.vmacro table I see all the macros I created with the select into: but when I try to access to them but without success

Edit: I got it. There was a sintax issue, now the code iterates but without stores de results of the query in the macro variables counting1,counting2 and so on

%macro countobs(table) ;

proc sql noprint;

%do i=1 %to 29;

select count (*) into :counting&i from dmpro.&&table&i;

%end;

quit;

%mend;

MPRINT(COUNTOBS): select count (*) into :counting25 from libname.table25;

Re: Help developing a macro with iterations

SergioSanchez — Wed, 10 Sep 2014 10:23:42 GMT

Hi Jaap

If I go to sashelp.Vmacro I can see all the macro variables I created with the select into.

The problem is that I don't know how to call that macros (table1,table2, and so on)

Re: Help developing a macro with iterations

SergioSanchez — Wed, 10 Sep 2014 07:45:28 GMT

Hi all

Thanks Hima for your effort but my macro's level is not enought for your code :smileycry::smileycry:, but it's much apreciated for me.

ballardw, that is not he problem as you can see in the code below

Tom, I have to run several test that count observations in various dataset so First I import the results from past days to a dataset, the spreedsheet is like this:

test table descript count date

=== ===== ======== ===== ====

test1 table1 zzzzzzzz 25 10/09/2014

.... .... .... .. .....

What I'm trying to do is take test1 to test 50, table1 to table50, descript1 to descript50 and convert into macro variables throught the proc sql statement select into.

After this I'm thinking about get the count value for each test and table and store it in others macrovariables.

Finally, with the values of count1-count50 perform a proc sql insert into, into the dataset I imported from excel to acumulate the results of the days and export again to excel file.

So I have managed the first step with de proc sql I posted in the post above

The second step is to count the observations and here is where I locked because when I try to run

76 %countobs

MLOGIC(COUNTOBS): Beginning execution.

MPRINT(COUNTOBS): proc sql;

MLOGIC(COUNTOBS): %DO loop beginning; index variable I; start value is 1; stop value is 29;

by value is 1.

NOTE: Line generated by the invoked macro "COUNTOBS".

3 select count (*) into :counting&i from dmpro.&table.i;

WARNING: Apparent symbolic reference TABLA not resolved.

MPRINT(COUNTOBS): select count (*) into :counting1 from dmpro.&;

ERROR 22-322: Expecting a name.

ERROR 76-322: Syntax error, statement will be ignored.

MLOGIC(COUNTOBS): %DO loop index variable I is now 2; loop will iterate again.

NOTE: Line generated by the invoked macro "COUNTOBS".

5 select count (*) into :counting&i from dmpro.&table.i;

WARNING: Apparent symbolic reference TABLA not resolved.

MPRINT(COUNTOBS): select count (*) into :counting2 from dmpro.&;

ERROR 22-322: Expecting a name.

ERROR 76-322: Syntax error, statement will be ignored.

But if I write %put _user_ I can see all the tables (from table1 to table50) with its values

Regards

Help developing a macro with iterations

SergioSanchez — Tue, 09 Sep 2014 16:18:34 GMT

I have a table with macro variables created throught a proc sql

proc sql inobs=50;

select trim(testnumber), trim(table) into :p1-:p50,

:table1-:tabla50

from testimportcont;

quit;

and I'd like to iterate through the table ir order to store new macro variables

I'm trying

%macro counting;

proc sql;

%do i=1 %to 50;

select count (*) into :counting1-:counting50 from libtest.&tabla&i;

%end;

quit;

%mend;

but it doesn't work and I don't know how to continue....

One of my weakness are to work with macros, the other are iterate with variables

Thanks in advanced

Re: Insert an automatic macro variable as a value in proc sql

SergioSanchez — Tue, 09 Sep 2014 12:07:10 GMT

Thanks both for your quickly answer.

Insert an automatic macro variable as a value in proc sql

SergioSanchez — Tue, 09 Sep 2014 11:40:48 GMT

Hi all

I am trying to insert values in a table with proc sql using a macro variable like this

proc sql;

insert into dummy (a, b, c, d, date)

values (&a., &b., &c., &d., &SYSDATE9);

quit;

Is there any option to achiveve the goal?. I try too with symget'SYSDATE9' and something else without success.

Thanks in advanced.

Re: Proc Datasets with where statement?

SergioSanchez — Wed, 27 Aug 2014 11:39:20 GMT

RW9, I have installed the ODBC driver and it's too slow but thanks for your tip.

Ok, so data step it's not bad.

Thanks guys for your help

Regards