Good afternoon,
I am trying to split a table with 800 million records and 48 variables into 4 tables so we can reduce the size and send compressed via FTP. I am currently doing the split individually and creating the 4 files by the codes below. Is there an easy way to create the 4 files by just reading from the MASTERFILE once . Or,is there a more efficient code to do this as we need to break up several big tables . I am a basic user of SAS and appreciate any assistance.
Thank you as always!
% let header_1 = variable1......variable12
% let header_2 = variable13.....variable24
% let header_3=variable25......variable36
% let header_4 = variable37....variable48
data step FILE1;
set MASTERFILE(keep=&header_1);
run;
data step FILE2;
set MASTERFILE(keep=&header_2);
run;
data step FILE3;
set MASTERFILE(keep=&header_3);
run;
data step FILE4;
set MASTERFILE(keep=&header_4);
run;
Hi @lmtamina Why not keep it simple by-
/*This reads only once*/
data FILE1 (keep=&header_1)
FILE2(keep=&header_2)
FILE3(keep=&header_3)
FILE4(keep=&header_4);
set MASTERFILE;
run;
@lmtamina wrote:
We are splitting by column as I understand so we do not have to run the queries to produce the master table as it takes a long time to run. We are adding a unique variable to all the 4 files so when our vendor receive them they can tie the records together using this variable. Does that make sense?
Sounds scary to me actually. I also am having a hard time understanding how this is supposed to save time.
Is your "unique variable" a unique row identifier from the source for each?
It is not an issue of the 6 items. You really need to post the actual code and log.
I would check your macro variables and see if there's something there causing the error/issue or if it works with just the 6th data set.
data demo1 (keep = make model origin)
demo2 (keep = mpg_highway mpg_city)
demo3 (keep = Cylinders Type Weight Wheelbase Drivetrain EngineSize HorsePower)
demo4 (keep = Invoice MSRP)
demo5 (keep = Length Weight) demo6 (keep = Cylinders DriveTrain);
set sashelp.cars;
run;
@lmtamina Can you "guarantee" LOTSSSSS of Memory(RAM). Sorry not meaning to shout with UPCASE, but I am really asking.
Also, what are the datatypes. Are those a mix of char/num. Is there a pattern in the arrangement?
Hi @lmtamina Why not keep it simple by-
/*This reads only once*/
data FILE1 (keep=&header_1)
FILE2(keep=&header_2)
FILE3(keep=&header_3)
FILE4(keep=&header_4);
set MASTERFILE;
run;
Please post the complete log (including the definition of the macro variables) into a box opened with the </> button.
In case I have not made myself clear:
USE THE </> BUTTON FOR POSTING LOGS!
PS including the definition of the macro variables
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.