Do Loop a code over set values from dataset

[Oferc]

Hello,

I have a code which I need to iterate it over a list of values from a dataset. This is the list of sochen_id sitting in work.distinct_values table : 

PROC SQL;
CREATE TABLE WORK.DISTINCT_VALUES AS
SELECT DISTINCT SOCHEN_ID FROM WORK.SOCHEN_SALES_OUTPUT_STRUC_3;
QUIT;

And this is the code which is working fine but I always need to put manually distinct value for var and run it.

The problem is I have 1000 distinct values to put in the var variable so naturally  I thinking on a loop here.

%LET VAR = 75267;

OPTIONS MSGLEVEL=I;

FILENAME SRC "Data Analytics\Template.Xlsx" RECFM=N;
FILENAME DEST "Data Analytics\Sochen_&VAR..Xlsx" RECFM=N;

DATA _NULL_;
LENGTH MSG $ 384;
RC=FCOPY('SRC', 'DEST');
IF RC=0 THEN
PUT 'COPIED SRC TO DEST.';
ELSE DO;
MSG=SYSMSG();
PUT RC= MSG=;
END;
RUN;


PROC EXPORT DATA=WORK.SOCHEN&VAR.SALES
DBMS=XLSX
OUTFILE="Data Analytics\Sochen_&VAR..Xlsx"
REPLACE;
SHEET='SALES';
RUN;

PROC EXPORT DATA=WORK.SOCHEN&VAR.NOVIDEA
DBMS=XLSX
OUTFILE="\Data Analytics\Sochen_&VAR..xlsx"
REPLACE;
SHEET='NOVIDEA';
RUN;

How do I rap all this in a do loop which will loop on the list of sochen_id values from the work.distinct_values dataset?

[Kurt_Bremser · Accepted solution]

Save this code (without the %LET) to a .sas file, e.g. export.sas. Then run this:

data _null_;
set work.distinct_values;
call execute('%nrstr(
  %let var=' !! strip(sochen_id) !! ';
  %include "export.sas";
)');
run;

This creates a sequence of %LET and %INCLUDE statements which will run after this data step finishes.

[acordes]

you're the MASTER.

the adaptation of your code saves me hundreds of lines of code.  

[Oferc]

That was so cool. Thanks!

[SASJedi]

You'll need a macro definition to work with iterative macro loops. Use PROC SQL SELECT - INTO syntax to write the values into aserise of macro variables, then iterate over them. 

Here is an example:

data WORK.SOCHEN_SALES_OUTPUT_STRUC_3;
 DO SOCHEN_ID = 75267 TO 75275 BY 3;
    OUTPUT;
    OUTPUT;
  END;
RUN;


%MACRO DOIT;
PROC SQL NOPRINT;
SELECT DISTINCT SOCHEN_ID
   INTO :VAR1- 
   FROM WORK.SOCHEN_SALES_OUTPUT_STRUC_3;
QUIT;

%do I=1 %to &SQLOBS;
  %PUT NOTE: This file is "\Data Analytics\Sochen_&&var&i...xlsx";
%end;
%mend;

%doit

From the Log:

NOTE: This file is "\Data Analytics\Sochen_75267.xlsx"
NOTE: This file is "\Data Analytics\Sochen_75270.xlsx"
NOTE: This file is "\Data Analytics\Sochen_75273.xlsx"