topic Re: How to split large dataset into multiple text files in SAS Procedures

How to split large dataset into multiple text files

JoseSAS79 — Sun, 29 Nov 2020 22:57:37 GMT

Hi All!

Just need some help in creating a macro for a large data set I have. The dataset has two columns 1)Month_Year (e.g 201511) 2) IP address . For example

Month_Year IP

201307 xxx.1233.xxxx.

201804 364.xxx.277

201702 947.3734.xxxx

201804 73.6282.xxx

I want a macro that creates a file for each month_year containing a list of the IP relating to that month.

So far this is what I've got :

proc sql no print;

select distinct month_year into : month_year

separated by ' '

from mydata

order by month_year;

quit;

%macro test;

%do i=1 %to %sysfunc (countw(&month_year));

%let k= %sysfunc (scan(&&month_year,&i));

data data_want_&k.;

set mydata

if month_year ="&k.";

proc export data = data_want_&k.

outfile = "\\C:\My folder\&k.

dbms= tab replace;

putnames=NO;

run;

%end

%mend;

%test;

This is what I have tried running - I'm not getting any errors, but I'm also not getting any output.

Any help will be much appreciated!

Re: How to split large dataset into multiple text files

Kurt_Bremser — Sun, 29 Nov 2020 23:05:40 GMT

No macro needed. Multiple text files can be written in one data step, using a FILEVAR= option in the FILE statement.

Sort by month_year first, and use

by month_year;
if first.month_year then /* set FILEVAR= variable here */;

Re: How to split large dataset into multiple text files

mkeintz — Mon, 30 Nov 2020 04:26:52 GMT

Here's a more complete example using @Kurt_Bremser 's suggestion, but on the sashelp.citiday dataset. This code uses the "mod" option on the file statement, which means you can write intermittently to any of the raw output files without previous output being overwritten every time an output file is re-opened (which is the default behavior of the file statement). The benefit would be that pre-sorting by year/month would not be needed. There is one file for each year/month combination extracted from the variable DATE:

data _null_;
  set sashelp.citiday;
  fvar=cats('c:\temp\',year(date),'_',put(month(date),z2.),'.csv');
  file txt disk filevar=fvar mod dlm=',';
  put date snydjcm;
run;

Of course, using MOD means you probably want to delete the output files (c:\temp\1988_01.csv, c:\temp\1988_02.csv, ... for the above example) prior to running the data step.

Re: How to split large dataset into multiple text files

JoseSAS79 — Mon, 30 Nov 2020 10:02:30 GMT

Thanks so much for the reply. I've never really used filevar, so I've put it together below after reviewing a few posts in the forum. The final text files should only have a list of IP addresses. Please let me know if I've got the idea:

PROC SORT DATA= Mydata OUT=Mydata_sorted;
BY month_year;
RUN;

DATA _NULL_;

SET mydata_sorted;
BY month_year;

if first.make then out_file=cats(‘/folders/myfolders/’, trim(month_year));

file temp filevar=out_file dlm=’,’ dsd;

if first.make then
put ‘IP';

put IP;

run;

The example I saw had two put statements as well - but don't really understand why.
Thanks in advance!

Re: How to split large dataset into multiple text files

Kurt_Bremser — Mon, 30 Nov 2020 10:19:46 GMT

The FIRST. variables are created for the variables in the BY statement, so your code should be this:

data _null_;
set mydata_sorted; 
by month_year; 
if first.month_year then out_file = cats(‘/folders/myfolders/’, month_year);
/* no TRIM needed, CATS does that on its own */
file temp filevar=out_file dlm=’,’ dsd; /* do you need a delimiter with only one column? */
if first.month_year then put ‘IP'; /* creates a header in each file */
put IP;
run;

Re: How to split large dataset into multiple text files

Tom — Mon, 30 Nov 2020 18:26:48 GMT

I wouldn't make the filename conditionally.

out_file = cats('/folders/myfolders/', month_year,'.txt');

If you do then you need to either RETAIN the variable

retain out_file;

or only execute the FILE statement once per group.

if first.month_year then do;
  out_file = cats('/folders/myfolders/', month_year,'.txt');
  file out filevar=out_file dlm=’,’ dsd; 
end;

PS Some Microsoft "stupid" quotes got mixed into your posted code.

Re: How to split large dataset into multiple text files

Kurt_Bremser — Mon, 30 Nov 2020 18:33:43 GMT

Good catch. Better to set the filevar unconditionally. And those funny quotes come from @JoseSAS79's post, as I copy/pasted the code directly from there.

To @JoseSAS79 never move code through any kind of word processor.