topic Re: Do Loop a code over set values from dataset in SAS Programming

Do Loop a code over set values from dataset

Oferc — Thu, 10 Mar 2022 13:54:37 GMT

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?

Re: Do Loop a code over set values from dataset

Kurt_Bremser — Thu, 10 Mar 2022 14:22:04 GMT

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.

Re: Do Loop a code over set values from dataset

SASJedi — Thu, 10 Mar 2022 14:23:41 GMT

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"

Re: Do Loop a code over set values from dataset

acordes — Thu, 10 Mar 2022 14:51:47 GMT

you're the MASTER.

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

Re: Do Loop a code over set values from dataset

Oferc — Sun, 13 Mar 2022 06:42:28 GMT

That was so cool. Thanks!