BookmarkSubscribeRSS Feed
wer61537
Calcite | Level 5

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/2709...

 


%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;

1 REPLY 1
Reeza
Super User

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;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 617 views
  • 0 likes
  • 2 in conversation