topic Iterate a dataset and export ranges of records to files in SAS Programming

Iterate a dataset and export ranges of records to files

wer61537 — Fri, 10 Jun 2016 17:59:57 GMT

I have this loop within proc sql that iterates the dataset pulling ranges of deciles to export to files by decile range. The loop works, but including proc export, the loop continues but ony creates the first file. This is contrary towhat I read in https://communities.sas.com/t5/Base-SAS-Programming/Export-multiple-tables-from-SAS-to-csv/td-p/27090.

%macro t();
proc sql outobs=10;

select max(row) into :n
from Temp7.Deciles;

%do i=1 %to &n.;
%put &i;
select
seg_n,
start_decile,
end_decile, file_n, file_nQ
into :seg_n, :start_decile,:end_decile, :file_n,:file_nQ
from Temp7.Deciles where row = &i;

/*create table temp7.&seg_0_100.*/
CREATE TABLE Temp7.&seg. AS
select distinct
GST_REF_I,
sales_str,
sales_web,
total_sales,
&seg_n. as segment_id,
pull_date
from SLS_RANK
where seg_n =&segnameQ. /*parent_seg_n to prevent pulling data for a failed previous run*/
and overall_quantile between &start_decile. and &end_decile. ;
);

proc export data=Temp7.&seg.
(keep=gst_ref_i segment_id pull_date)
outfile="&path./&file_n."
dbms=dlm replace;
delimiter='|';
run;
%end;
quit;
%mend t;

Re: Iterate a dataset and export ranges of records to files

Reeza — Fri, 10 Jun 2016 19:04:29 GMT

I think it's your proc sql boundaries that are causing the issues. Your loop starts inside the sql step but then the proc export is outside. This won't work. Add a new proc sql inside the loop.

EDIT: You also appear to have an extra parentheses in the code, I've removed it below. I have NOT checked the rest of your code for accuracy, you may still have errors, but your log should help with that.

%macro t();
proc sql outobs=10;

select max(row) into :n
from Temp7.Deciles;
quit;

%do i=1 %to &n.;
%put &i;

proc sql;
select
seg_n,
start_decile,
end_decile, file_n, file_nQ
into :seg_n, :start_decile,:end_decile, :file_n,:file_nQ
from Temp7.Deciles where row = &i;


/*create table temp7.&seg_0_100.*/
CREATE TABLE Temp7.&seg. AS
select distinct
GST_REF_I,
sales_str,
sales_web,
total_sales,
&seg_n. as segment_id,
pull_date
from SLS_RANK
where seg_n =&segnameQ. /*parent_seg_n to prevent pulling data for a failed previous run*/
and overall_quantile between &start_decile. and &end_decile. ;


quit;
 
proc export data=Temp7.&seg.
(keep=gst_ref_i segment_id pull_date)
outfile="&path./&file_n."
dbms=dlm replace;
delimiter='|';
run;
%end;

%mend t;