Hi
I am looking for some optimize techniques to solve the memory issue in Workspace library.
I am using couple of techniques like
1.Option Compress = Yes;
2.DROP in SQL statement.
Anyone has any other way pls let me know.
Regards
Sourav
First of all, the WORK library is not a memory issue, but a disk storage issue.
Since there are a gazillion possible reasons for an overflow in the WORK library, you need to be much more specific.
Which operation fails, and at which part of the code? Post the log.
If the intent of your code is not clearly discernable from the code you post, add a description of the task. May well be that another method to achieve your goal needs less resources.
Just to add what @Kurt_Bremser said:
Try and look at the % of storage used of the work directory and if it hits 100% than you have to increase your storage or decrease your data.
If you are on AIX you can use df -g .
Hey Kurt
Thanks for your observation here.
I am giving scenario here.
I am using this code inside a report
PROC SQL;
CREATE TABLE WORK.TECH AS
SELECT a.* ,
B.IP_SICSID ,
b.MAIN_CURRISOALPHA3_ID ,
CASE WHEN b.LEVEL_OF_BUS_ID = "IAB" THEN "inward"
WHEN b.LEVEL_OF_BUS_ID = "ORP" THEN "outward"
ELSE ''
END as inw_outw format $10. ,
b.BUS_ID ,
b.TYPE_OF_BUS_ID ,
b.BUS_TTL ,
%IF &PROFIT_CENTER = Y %THEN %DO;
b.PRFT_CENT_TTL ,
%END;
/* DWH-2811 Change starts */
%IF &INSURED_ID_TTL = Y %THEN %DO;
b.INS_ID ,
b.INS_TTL ,
%END;
%IF &IP_FROM_TO = Y %THEN %DO;
b.IP_FROM ,
b.IP_TO ,
%END;
b.PAYM_PARTN_ID ,
b.PAYM_PARTN_TTL ,
b.ACCOUNT_GROUP_ID ,
b.LEVEL_OF_BUS_ID ,
b.BUS_PARTN_ID_BOOK ,
b.ORIG_CDNT_ID ,
b.ORIG_CDNT_TTL ,
b.ORIG_CDNT_COUNTRY
FROM WORK.AGING_TECHNICAL a INNER JOIN
WORK.IP_SECT_SMALL b on
a.SECTION_SICSID=b.SECT_SICSID
;
QUIT;
This code is part of the report.
After running this part I ma getting observation with maximum selection as
We don't have much space/memory in DEV environment Workspace. For that it is giving us error as
In the whole report I have many other joins like the above there also observations like millions of. I am thinking if I will go for data step instead of SQL join, but there data will not be same, because merging and inner join both are not same... For that I am looking for some optimized method.. I can not remove the variables also, because we need in the report minimum 50 column. It will be varied as per the selection, sometime more than 60.
Please suggest me some way according to you.. If we can do something code except increase the space technically, then it will be great.
I want try the best level I can..
Thanks
Regards
Sourav
What about using a permanent SAS library instead of WORK for some of your datasets - is that an option or are you space-limited there as well?
I see no dataset tech_m in your code, but in the log. Please post code and log that correspond to each other, or we will unnecessarily be forced to make guesses.
From the log, it looks as if there's a one-to-many relationship, which would allow the use of sort and data steps. These are usually less resource-hungry.
Based on your sample program, here's one way to proceed. Break up the query into two steps. In the first step (saved as a view instead of a table), subset source B down to just the records and fields that will be needed. Then use a second step to join with A. The first step might look like this:
CREATE view TECH AS
SELECT
IP_SICSID ,
MAIN_CURRISOALPHA3_ID ,
CASE WHEN LEVEL_OF_BUS_ID = "IAB" THEN "inward"
WHEN LEVEL_OF_BUS_ID = "ORP" THEN "outward"
ELSE ''
END as inw_outw format $10. ,
BUS_ID ,
TYPE_OF_BUS_ID ,
BUS_TTL ,
%IF &PROFIT_CENTER = Y %THEN %DO;
PRFT_CENT_TTL ,
%END;
/* DWH-2811 Change starts */
%IF &INSURED_ID_TTL = Y %THEN %DO;
INS_ID ,
INS_TTL ,
%END;
%IF &IP_FROM_TO = Y %THEN %DO;
IP_FROM ,
IP_TO ,
%END;
PAYM_PARTN_ID ,
PAYM_PARTN_TTL ,
ACCOUNT_GROUP_ID ,
LEVEL_OF_BUS_ID ,
BUS_PARTN_ID_BOOK ,
ORIG_CDNT_ID ,
ORIG_CDNT_TTL ,
ORIG_CDNT_COUNTRY
FROM WORK.IP_SECT_SMALL
where SECT_SICSID in
(select distinct SECTION_SICSID from WORK.AGING_TECHNICAL)
;
Then combining with A might be an inner join or might be simpler, along the lines of:
create table want as select * from TECH a, WORK.AGING_TECHNICAL b where a,SECTION_SICSID = b.SECT_SICSID;
Obviously this is untested. If the data set relative sizes are different, you might reverse the subsetting and use step 1 to select only the records from A that will be needed.
My first question would be: Why are you running out of Work/Utility space? Is there a quota set for you as user? Or is Work eventually cluttered with leftovers from crashed EG sessions? Have you already contacted your SAS admin to try and get the workspace you need.
As for your code: What's the size of the two tables (in GB). If one of the two tables is small enough to fit into memory then you could go for a hash lookup and avoid sorting at all.
Another option could be to create indices for the two tables you want to join. I believe that could avoid sorting as well.
Tried it. Didn't make a difference in my tests.
For one-time-use-data-sets, create a view instead of a data set.
Another option is to cleanup as you go, assuming you don't need any of the earlier data:
proc datasets library = work kill;
run;
At the end of the day these options don't help if you have way too little SAS WORK space to start with.
Only keep variables that you absolutely MUST have in your data set
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: