How to split large dataset into multiple text files

[JoseSAS79]

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!

[Kurt_Bremser]

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 */;

[JoseSAS79]

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!

[Kurt_Bremser]

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;

[Tom]

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.

[Kurt_Bremser]

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.

[mkeintz]

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.