topic Re: Code running slow in SAS in SAS Programming

Code running slow in SAS

EJAA — Sat, 28 Oct 2017 05:42:43 GMT

Hi All,

I'm running the codes below on two data sets but its taking extremely long to process. I don't really know what is wrong with my codes that is taking too long to process when i run. I have about 9700 firms in one data set and in another data set a list of firms connected to each of the 9700 firms. Due to the slowness i did run from 1-50 firms but that is even not processing after a day. The code gives no errors. Any advice for me to correct the slowness. Thanks in advance for the help. Regards ejaa.

/*****getiing the count of number of lpermno in help*****/
proc sql noprint; select count(lpermno) into: A from ase.help ; quit ;

%put &A ;

/********getting a sequence order to observations**********/
Data ase.help ;
Set ase.help;
Retain seq 0 ;
Seq = seq + 1 ;
run ;

/*******Transpose help to get long format*********************/
proc transpose data=ase.help out=ase.help_trans;
by lpermno seq;
run;

Data ase.help_trans (Rename = (COL1 = companyid)) ;
Set ase.help_trans ;
Where COL1 ne . ;
Run ;

%let C1 = 1 ;
%let c2 = 50 ;

/***********Separating each set of firms attched to lpermno*************/

%macro rpt;

%Do R = &C1 %to &C2;

data ase.lp&R. (rename = (Seq = seq&R.)) ;
Set ase.help_trans ;
Where seq = &R ;
Run ;

proc sort data = ase.lp&R.; by companyid ; run ;
%end ;
%mend rpt ;

%rpt ;

/***********Merge separated sets to help1******************/

Proc sort data = ase.help1 ; By companyid ; run ;

%macro rpt1;
%Do R = &C1 %to &C2;
data ase.com_1 ;
Merge ase.lp1 - ase.lp&R. ;
By companyid ;
run ;
%end ;
%mend rpt1 ;

%rpt1 ;

Proc sort data = ase.com_1 ; By companyid ; run ;

data ase.com ;
Merge ase.help1(in= a drop = lpermno ) ase.com_1 ;
By companyid ;
If a ;
run ;

/**************Select different lpermno from help********************/

%macro rpt2;
%Do R = &C1 %to &C2;
Proc sort data = ase.lp&R. nodupkey out = ase.f&R.; By lpermno ; run ;
%end ;
%mend rpt2 ;

%rpt2 ;

/***************************merge lpermno from help to help 1 *****************************/
Proc sort data = ase.help1 ; By lpermno ; run ;

%macro rpt3;
%Do R = &C1 %to &C2;
Data ase.Com_long1 ;
merge ase.f1 - ase.f&R. ;
by lpermno ;
Run ;
%end ;
%mend rpt3 ;

%rpt3 ;

Data ase.Com_long ;
merge ase.help1 (in = a) ase.Com_long1 ;
by lpermno ;
If a ;
Run ;

/*************Getting different sets of firms out of help1***********************/
%macro rpt4;
%Do R = &C1 %to &C2;
data ase.sum&R. ;
Set ase.com ;
where seq&R. = &R ;
Run ;
Proc sort data = ase.sum&R. ; By date ; run ;
%end ;
%mend rpt4 ;

%rpt4 ;

/*******getting mean prices by date in all the sets*************************/
%macro rpt5;
%Do R = &C1 %to &C2;
proc summary data = ase.sum&R. ;
by date;
var vol;
output out=ase.sum_&R. mean=beta_vol ;
run;
%end;
%mend rpt5 ;

%rpt5 ;

/********************separating lpermno to different sets from help1*********************************/
%macro rpt6;
%Do R = &C1 %to &C2;
data ase.price&R. ;
Set ase.Com_long ;
where seq&R. = &R ;
Run ;
%end ;
%mend rpt6 ;

%rpt6 ;

/***********************Merging matching lpermno and attched firms by date*********************************/
%macro rpt7 ;
%Do R = &C1 %to &C2;
data ase.final&R.(drop = _TYPE_ _FREQ_ _NAME_ ) ;
Merge ase.price&R. (in = a) ase.sum_&R. (in = b) ;
By date ;
If a and b ;
run ;
%end ;
%mend rpt7 ;

%rpt7 ;

/************************Getting regression of each set**********************************/
%macro rpt8;
%Do R = &C1 %to &C2;
proc reg data=ase.final&R. outest = ase.result&R. ;
By lpermno ;
model vol = beta_vol stk ;
run;
%end ;
%mend rpt8 ;

%rpt8 ;

/**********getting results together****************************/

%macro rpt9;
%Do R = &C1 %to &C2;
Data ase.reg1 ;
Set ase.result1 - ase.result&R. ;
run;
%end ;
%mend rpt9 ;

%rpt9 ;

/**********************END********************************/

Re: Code running slow in SAS

ChrisBrooks — Sat, 28 Oct 2017 08:54:02 GMT

If you can't get the program to finish my advice would be to run it step by step until you find something that doesn't finish - my guess is it'll be the Proc Reg but if you run it as I suggest at least you'll know where to concentrate your efforts.

Re: Code running slow in SAS

jklaverstijn — Sat, 28 Oct 2017 10:22:47 GMT

Examine your log and look at the "real time" lines after each step. Especially the ones that show a high ratio between real time and cpu time are suspect and candidates for improvement. Also explain your data in more detail. Are they SAS datasets or tables in a DBMS? Do you use indexes? Maybe sorting will help? Performance is an elaborate field of expertise and lots of info is required before anyone can help. Share a sample of your data and above all your logs in this forum. The first commandment of performance analysis is " Thou Shalt Read Log Files". Use FULLSTIMER and MSGLEVEL=I options for more detailed info. And in case of macro's, use at least the MPRINT, SYMBOLGEN and MLOGIC options as well.

Hop this helps,

- Jan.

Re: Code running slow in SAS

Astounding — Sat, 28 Oct 2017 14:20:48 GMT

Just spot-checking the program, the design looks like it will take about 25 times what it needs to. Let me give you just a couple of examples, so you can rethink the process.

Example 1: %RPT

This step reads the data set ASE.HELP_TRANS 50 times, once for each iteration of the %DO loop. Better planning would read the data set just once, and would create all 50 data sets with one pass through the data. Just a brief example to give you the idea:

proc sort data=ase.help_trans;

by company_id;

run;

data ase.lp1 (rename=(seq=seq1))

ase.lp50 (rename=(seq=seq50));

set ase.help_trans;

if seq=1 then output ase.lp1;

else if seq=50 then output ase.lp50;

run;

The use of macro language would get a little more complex than what you have now. Is that worth it, to cut out 95% of the time?

By sorting first, you can eliminate the need for 50 sorts (albeit on smaller data sets) later.

Example 2: %RPT1

It's not clear if this step contains a typo, because it re-creates the same data set 50 times. Each iteration replaces ASE.COM_1 with a new version. If that's the intent, there is no need for the first 49 iterations. If that's a typo, the program is still inefficient. Assuming that the output should actually be named ASE.COM_&R the process should be simplified so that the interior of the loop reads:

data ase.com_&R ;
Merge ase.lp%EVAL(&R-1) ase.lp&R. ;
By companyid ;
run ;

The first iteration would need a little extra attention, so that you don't merge a data set with itself. But once you get to the 50th iteration, there is no need to merge 50 data sets. Just merge 2 data sets (one of which is the merged result from the 49th iteration).

In summary, these are just a couple of examples. The program was designed looking at only small pieces of the process and using macro language to repeat them 50 times. A better approach would look at the larger process, and design something that requires much less work on the computer's part.

Re: Code running slow in SAS

Reeza — Sat, 28 Oct 2017 18:02:36 GMT

How many rows are your data sets?

Re: Code running slow in SAS

ChrisNZ — Mon, 30 Oct 2017 03:16:33 GMT

What @Astounding said.

You seem to be doing the same thing over and over gain on tiny bits of data.

Please post and small data example of where you start from and what you want to obtain, and it can probably be derived in a few steps.

Re: Code running slow in SAS

EJAA — Mon, 30 Oct 2017 04:16:25 GMT

The number of rows for the data set with 9700 firms is 19725636 while the other is having 7927 rows. Thanks.

Re: Code running slow in SAS

EJAA — Mon, 30 Oct 2017 05:03:33 GMT

Hi ChrisNZ

Thanks for the direction.

Please attached sample data.Thanks in advance for the help. regards

Re: Code running slow in SAS

ChrisNZ — Mon, 30 Oct 2017 05:25:20 GMT

Please don't ask us to do all your work.
Please post a small data example of where you start from and what you want to obtain. Typically 100 observations is sufficient.

Re: Code running slow in SAS

EJAA — Mon, 30 Oct 2017 05:52:43 GMT

Thanks ChrisNZ.

Attached is a small sample of my dataset with reference to the nature of the data. In the end i want to obtain regression output for each firm. Thanks.

Re: Code running slow in SAS

ChrisNZ — Mon, 30 Oct 2017 20:37:28 GMT

Feel free to not bother supplying the basic information that's necessary to answer your question, and to not getting answers.

Here's an example of how it's done: https://communities.sas.com/t5/General-SAS-Programming/Creating-time-intervals-based-on-two-date-variables/m-p/406737#M51131

Re: Code running slow in SAS

EJAA — Tue, 31 Oct 2017 02:29:10 GMT

Hi ChrisNZ.

Thanks once again.

Following your direction, this is how my dataset looks like:

Data set “help” contains the number of firms connected to each firm.d1-d3 are companyid’s of firms connected to for instance firm with lpermno 10001.

CompanyID   lpermno           d1                 d2
10675      10001          8433        1723398
620983      10002          576969    8433
959            10025     576969      1723398
9137          10028     2070824 18195

Data set “help1” contains daily volume and skt_returns for 9700 firms.

CompanyID   lpermno           date              volume           stk_returns
10675      10001          01/03/2000 142             0.40
10675        10001           01/04/2000    245                 0.45
10675       10001           01/05/2000    285                 0.42
10675        10001     01/07/2000   181                    0.43

10675      10001          01/10/2000 149             0.44
620983    10002          01/03/2000 200             0.30
620983   10002           01/04/2000    280                 0.35
620983    10002           01/05/2000    230                 0.32
620983     10002     01/07/2000   190                    0.33

620983 10002 01/10/2000 240 0.34

8433        10003          01/03/2000 300             0.10
8433         10003           01/04/2000    380                 0.15
8433        10003          01/05/2000    330                 0.12
8433         10003     01/07/2000   390                    0.13

8433 10003 01/10/2000 340 0.14

576969     10004          01/03/2000 400             0.80
576969    10004           01/04/2000    480                 0.85
576969      10004          01/05/2000    430                 0.82
576969      10004     01/07/2000   490                    0.83

576969 10004 01/10/2000 440 0.84

1723398     10000          01/03/2000 500             0.60
1723398    10000           01/04/2000 580                 0.65
1723398      10000          01/05/2000    530                 0.62
1723398     10000     01/07/2000   590                    0.63

1723398 10000 01/10/2000 500 0.64

Final Table for firm with id 10001.

CompanyID   lpermno           date              volume           stk_returns beta_volume (obtained from firms connected to firm 10001)
10675      10001          01/03/2000 142             0.40 21
10675        10001           01/04/2000    245                 0.45           30
10675       10001           01/05/2000    285                 0.42 40
10675        10001     01/07/2000   181                    0.43 84

10675 10001 01/10/2000 149 0.44 41

From data set help, I first create a portfolio of firms connected to each firm from data set help1 to obtain portfolio of their volume called beta_volume (mean of volume for each day). I then created dataset for each firm obtain the "final table" shown above. In the end, I run a regression where I regress volume on beta volume and stk_returns for each firm. This the work i used the earlier codes i posted to do.Thanks very much.

regards

ejaa.

Re: Code running slow in SAS

ChrisNZ — Tue, 31 Oct 2017 05:24:21 GMT

Please provide a sample small enough that we know how the value 21 (beta_volume) that you are showing in the desired output is calculated. We can't see where it comes from here.

Re: Code running slow in SAS

EJAA — Tue, 31 Oct 2017 05:34:20 GMT

Please the beta_volume is the daily average/mean of the firms connected to a specific firm. So for instance in the example given, firm 10001 is connected to two firms thus 8433 & 1723398. Hence i obtain the average of daily volume of these two firms and link it to firm 10001 by date to produce the final table to run the regression.

In my earlier code, this what i used to obtain the mean of firms connected to a specific firm.

%macro rpt5;

%Do R = &C1 %to &C2;

proc summary data = ase.sum&R. ;
by date;
var vol;
output out=ase.sum_&R. mean=beta_vol ;
run;

%end;
%mend rpt5 ;

%rpt5 ;

Thanks very much.

Re: Code running slow in SAS

ChrisNZ — Wed, 01 Nov 2017 03:38:25 GMT

I am about to quit trying, The small example you supplied does not show the link you mention between firm 10001 and 8433 & 1723398, and does not show volumes that would explain how 41 is attained.

Re: Code running slow in SAS

EJAA — Wed, 01 Nov 2017 03:52:12 GMT

Thanks ChrisNZ for the great efforts.

Please when we look into dataset "help" we see that firm 10001 is having firms with in colum d1 and d2 with companyid 8433 & 1723398. Thus these two firms are connected to firm 10001. On the beta volume sorry abt that, it should be as shown below. Thus the average daily volume of the two firms connected to firm 10001. Thanks once again.regards

Final Table for firm with id 10001.

CompanyID   lpermno           date              volume           stk_returns beta_volume (obtained from firms connected to firm 10001)
10675      10001          01/03/2000 142             0.40 400 (thus 300+500/2)
10675        10001           01/04/2000    245                 0.45           480
10675       10001           01/05/2000    285                 0.42   430
10675        10001     01/07/2000   181                    0.43   490

10675 10001 01/10/2000 149 0.44 420

Re: Code running slow in SAS

ChrisNZ — Wed, 01 Nov 2017 05:00:06 GMT

We are getting there.

Please tell me how this performs:


data HELP;
 input COMPANYID   LPERMNO  D1  D2  ;                       
cards;
10675    10001   8433     1723398   
run;
 
data HELP1;
input COMPANYID   LPERMNO   DATE : mmddyy10.  VOLUME  STK_RETURNS ;
cards;
10675    10001     01/03/2000    142     0.40
10675    10001     01/04/2000    245     0.45         
10675    10001     01/05/2000    285     0.42
10675    10001     01/07/2000    181     0.43      
10675    10001     01/10/2000    149     0.44
8433     10003     01/03/2000    300     0.10
8433     10003     01/04/2000    380     0.15         
8433     10003     01/05/2000    330     0.12
8433     10003     01/07/2000    390     0.13      
8433     10003     01/10/2000    340     0.14
1723398  10000     01/03/2000    500     0.60
1723398  10000     01/04/2000    580     0.65         
1723398  10000     01/05/2000    530     0.62
1723398  10000     01/07/2000    590     0.63      
1723398  10000     01/10/2000    500     0.64
run;

data WANT;
  set HELP1;
  if _N_=1 then do;
    dcl hash H(dataset:'HELP');
    H.definekey('LPERMNO');
    H.definedata('D1','D2');
    H.definedone();
    dcl hash H1(dataset:'HELP1(rename=(VOLUME=BETA))');
    H1.definekey('COMPANYID','DATE');
    H1.definedata('BETA');
    H1.definedone();
    call missing(D1,D2,BETA);
  end;
  call missing(D1,D2,BETA,BETA1,BETA2);
  rc=H.find();
  rc=H1.find(key:D1,key:DATE );
  BETA1=BETA;
  rc=H1.find(key:D2,key:DATE );
  BETA2=BETA;
  AVG=(BETA1+BETA2)/2;
run;

I left the intermediate calculations in the final table.

COMPANYID	LPERMNO	DATE	VOLUME	STK_RETURNS	D1	D2	BETA	BETA1	BETA2	AVG
10675	10001	14612	142	0.40	8433	1723398	500	300	500	400
10675	10001	14613	245	0.45	8433	1723398	580	380	580	480
10675	10001	14614	285	0.42	8433	1723398	530	330	530	430
10675	10001	14616	181	0.43	8433	1723398	590	390	590	490
10675	10001	14619	149	0.44	8433	1723398	500	340	500	420

Re: Code running slow in SAS

ChrisNZ — Thu, 02 Nov 2017 04:42:18 GMT

@EJAA So?

Using 8k and 20m rows respectively, this takes 1min 20s on my machine.

data HELP (keep=COMPANYID LPERMNO D1 D2)
     HELP1(keep=COMPANYID LPERMNO DATE VOLUME);
  do COMPANYID= 1 to 8000;
    LPERMNO=COMPANYID+100000;
    D1     =int(ranuni(0)*8000);
    D2     =int(ranuni(0)*8000);
    output HELP;
    do DATE='01jan2010'd to '01jan2017'd;
      VOLUME=int(ranuni(0)*999);
      output HELP1;
    end;
  end;
  format DATE date9.;
run;


data WANT;
  if _N_=1 then do;
    dcl hash H(dataset:'HELP');
    H.definekey('LPERMNO');
    H.definedata('D1','D2');
    H.definedone();
    dcl hash H1(dataset:'HELP1(rename=(VOLUME=BETA))');
    H1.definekey('COMPANYID','DATE');
    H1.definedata('BETA');
    H1.definedone();
  end;
  set HELP1;
  call missing(D1,D2,BETA,BETA1);
  RC    = H.find();
  RC    = H1.find(key:D1,key:DATE );
  BETA1 = BETA;
  RC    = H1.find(key:D2,key:DATE );
  AVG   = (BETA+BETA1)/2;
  keep COMPANYID LPERMNO DATE VOLUME AVG;
run;

NOTE: There were 8000 observations read from the data set WORK.HELP.
NOTE: There were 20464000 observations read from the data set WORK.HELP1.
NOTE: Missing values were generated as a result of performing an operation on missing values.
Each place is given by: (Number of times) at (Line):(Column).
5116 at 45:15
NOTE: There were 20464000 observations read from the data set WORK.HELP1.
NOTE: The data set WORK.WANT has 20464000 observations and 10 variables.
NOTE: Compressing data set WORK.WANT decreased size by 27.29 percent.
Compressed is 9101 pages; un-compressed would require 12517 pages.
NOTE: DATA statement used (Total process time):
real time 1:19.48
user cpu time 1:16.51
system cpu time 2.65 seconds
memory 1313268.82k
OS Memory 1341000.00k
Step Count 532 Switch Count 267

Re: Code running slow in SAS

EJAA — Thu, 02 Nov 2017 06:57:25 GMT

Hi ChrisNZ

I sincerely apologies for the late update. I had few issues yesterday and was off hence me not updating you with reference to the codes you sent. Thanks very much for the understanding and really appreciate the great efforts.

Please just as you opinionated earlier on, we are getting there.

I have run the code and it worked perfectly on the sample above and is very fast of which i tried to follow the codes so i can modify the variables to enable me apply the codes to the bigger data i have but didnt work.

Please as i could only indicate the nature of the data when posting the question, I couldnt show much data but in my sample data HELP, the column runs from d1-d313 with most have value (companyid). Thus a specific firm could be linked to more than one firm. For instance a specific firm could be linked to just one while others two while others 20 etc. So firm with id 10001 is linked to more than two firms in the actual but couldnt show all as you requested i only post few sample for assessment and direction.Hence i have something like this below for data help

CompanyID   lpermno           d1                 d2 d3 d4 d5 d6 d7 d8 d9 d10
10675      10001          8433        1723398     2070824   18195
620983      10002          576969    8433
959            10025     576969      1723398
9137          10028     2070824 18195

8433 12540 576969

Also please in the data help1, aside this variables, i have other variables price, market_returns in addition to

CompanyID lpermno date volume stk_returns.

I have tried several times trying to follow your codes but still getting it wrong.

Please we are almost there and i seek for further direction to enable me apply on the main data as the codes work on the small sample data posted earlier. Thanks very much ChrisNZ for this great assistance.regards ejaa.

Re: Code running slow in SAS

jklaverstijn — Thu, 02 Nov 2017 13:06:28 GMT

Hi Ejaa,

More a comment on the datamodel: whenever I see a table that has columns like your d1-d313 a get the feeling that a transposition would make any processing a lot easier and your dataset less sparse. Eg, what happens if there is need for a d314? Compare these approaches:

CompanyID lpermno   d1   d2   
0675      10001     8433 1723398

with

CompanyID  lpermno   d
0675       10001     8433
0675       10001     1723398

See this paper for a more indepth discussion.

It would be an easy transformation and could greatly help speed up or at least simplify your code.

Regards,

- Jan