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?
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.
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.
you're the MASTER.
the adaptation of your code saves me hundreds of lines of code.
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"
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: