BookmarkSubscribeRSS Feed
mansinarang12
Calcite | Level 5

hi,

I am working on a huge data set with over 400 variables and around 99999 observations. It is a log detailing the usage of calls (local, international, duration, etc) , data, amount charged etc. I have another data set with the list of churners and the churn months. This data os given for 6 months and over 3 years separately.

1. I am not able to join the 2 data sets on the key variable : Product 

It always gives me insufficient memory error in log. Tried to run it on first 40 obs then also it did not work.

2. I have to create a data set which has the details of calls for only 3 selected months keeping everything else same.

 

Since loops are not allowed in proc sql, can anyone please help me out how to go about it?

4 REPLIES 4
Reeza
Super User
Neither of those datasets sounds large enough to run out of memory. What size GB are the SAS data sets? Also, please post your code.
mansinarang12
Calcite | Level 5

for joining of the data sets i used the code:

 

proc sql;
select c.*, s.*
from cnl as c right join smb as s
on s.product_id = c.product_id;
quit;

 

the size of smb is 1.2 gb and cnl is a few kbs.

 

for the second part where I need usage for only 3 selected months I do not know  how to go about it. Please help.

Patrick
Opal | Level 21

@mansinarang12

Not sure why you're running out of memory. For the SQL you've posted an alternative approach which should perform better would be using a hash lookup table. Condition for below code to work properly (as well as for your SQL): PRODUCT_ID is the primary key in both tables. 


data want(drop=_rc);
  if _n_=1 then
    do;
      if 0 then set cnl;
      dcl hash h (dataset:'cnl');
      h.defineKey('product_id');
      h.defineData(all:'y');
      h.defineDone();
    end;
  call missing(of _all_);

  set smb;
  _rc=h.find();
run;

 

ballardw
Super User

First thing with any date related topic: are you dates SAS date valued variables? That makes anything related to dates much easier in 99.9% of cases.

 

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 674 views
  • 0 likes
  • 4 in conversation