topic Re: macro loop in data set in SAS Programming

macro loop in data set

ERI_RAMIREZ — Mon, 13 Sep 2021 15:43:18 GMT

HI! I´m a begginer, and I want to use diferent data sets with similar syntax name, it only changes the month and year; i tried with this but When I try to view the data sets they dont exist. Can some one help me

%macro bdPrimasVertical;

%do yr=2019 %to %2021;
%do ms=10 %to %12;

data work.rescapi_&yr.._&ms. (keep=NoPoliza statuspoliza_orig FechaEmision fechavaluacion FechaFirmaCredito PlazoMeses FrecPago PrimaEmitida MontoSolicitado MontoSolicitadoSiniestro MontoPagadoSiniestro sumaasegurada_MN BEL_MN NuevaOPC_16_MN PVM TasaGPI CR_calc RC_calc CO_calc);
set D&yr..rescap_2019_&ms. (RENAME=(pe_2019_&ms.=PrimaEmitida));
run;

PROC SORT DATA=rescapi_&yr.._&ms.;
BY NoPoliza;
RUN;

%end;
%end;
%mend;

Re: macro loop in data set

sbxkoenk — Mon, 13 Sep 2021 15:51:11 GMT

Hello,

Try this:

%macro bdPrimasVertical;

%do yr=2019 %to 2021;
%do ms=10 %to 12;

data work.rescapi_&yr._&ms. 
(keep=NoPoliza statuspoliza_orig FechaEmision fechavaluacion FechaFirmaCredito 
      PlazoMeses FrecPago PrimaEmitida MontoSolicitado MontoSolicitadoSiniestro 
      MontoPagadoSiniestro sumaasegurada_MN BEL_MN NuevaOPC_16_MN PVM TasaGPI 
      CR_calc RC_calc CO_calc);
set D&yr..rescap_2019_&ms. (RENAME=(pe_2019_&ms.=PrimaEmitida));
run;

PROC SORT DATA=rescapi_&yr._&ms.;
BY NoPoliza;
RUN;

%end;
%end;
%mend;

%bdPrimasVertical

Koen

Re: macro loop in data set

Reeza — Mon, 13 Sep 2021 15:54:19 GMT

Instead can you leverage your naming convention with shortcut lists? Or can you query the name from sashelp.vtable into a macro variable instead? Both are simpler than macro loops within a data step.

Here is a reference that illustrates how to refer to variables and datasets in a short cut list:
https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

Hard to tell the issue with your current code - do you have the data sets in a library? It seems like you're using two periods which would imply a library reference but it doesn't seem to be correct. Can you post code that works - non macro code is fine - and indicate how you want to generalize it?

I made a rough attempt below but no way of knowing if it's right or not.

I've also added the macro debugging options (MPRINT/SYMBOLGEN) so you can use those to get more information in your log.

options mprint symbolgen;

%macro bdPrimasVertical;

%do yr=2019 %to %2021;
%do ms=10 %to %12;

data work.rescapi_&yr._&ms. (keep=NoPoliza statuspoliza_orig FechaEmision fechavaluacion FechaFirmaCredito PlazoMeses FrecPago PrimaEmitida MontoSolicitado MontoSolicitadoSiniestro MontoPagadoSiniestro sumaasegurada_MN BEL_MN NuevaOPC_16_MN PVM TasaGPI CR_calc RC_calc CO_calc);
set D&yr.rescap_2019_&ms. (RENAME=(pe_2019_&ms.=PrimaEmitida));
run;

PROC SORT DATA=rescapi_&yr._&ms.;
BY NoPoliza;
RUN;

%end;
%end;
%mend;

@ERI_RAMIREZ wrote:

HI! I´m a begginer, and I want to use diferent data sets with similar syntax name, it only changes the month and year; i tried with this but When I try to view the data sets they dont exist. Can some one help me

%macro bdPrimasVertical;

%do yr=2019 %to %2021;
%do ms=10 %to %12;

data work.rescapi_&yr.._&ms. (keep=NoPoliza statuspoliza_orig FechaEmision fechavaluacion FechaFirmaCredito PlazoMeses FrecPago PrimaEmitida MontoSolicitado MontoSolicitadoSiniestro MontoPagadoSiniestro sumaasegurada_MN BEL_MN NuevaOPC_16_MN PVM TasaGPI CR_calc RC_calc CO_calc);
set D&yr..rescap_2019_&ms. (RENAME=(pe_2019_&ms.=PrimaEmitida));
run;

PROC SORT DATA=rescapi_&yr.._&ms.;
BY NoPoliza;
RUN;

%end;
%end;
%mend;

Re: macro loop in data set

ballardw — Mon, 13 Sep 2021 16:17:20 GMT

Share your log.

If you have the correct code then this bit is suspect:

data work.rescapi_&yr.._&ms.

The second dot would make the data set name resolve to work.rescapi_2019._10 The first dot separates the library name from the data set name. The second dot in the resolved value should throw an error of Invalid data set name in the log.

Re: macro loop in data set

ERI_RAMIREZ — Mon, 13 Sep 2021 17:00:10 GMT

I have different data bases with the following nomenclature (rescap_&year_&month) in dif routes than only have the following nomenclature (D.$YEAR), Years going to 1930 to 2021 and month from 01 to 12. I need specific info from each data bases , then i need to put the results in one database together in vertical way by using a "set".

This exercise will be done as a acumulative process

I tried this for the following example and it works:

/*U N E R E S C A P 2 0 2 1*/

data WORK.rescap_2021_08 (keep=NoPoliza statuspoliza_orig FechaEmision fechavaluacion FechaFirmaCredito PlazoMeses FrecPago PrimaEmitida MontoSolicitado MontoSolicitadoSiniestro MontoPagadoSiniestro sumaasegurada_MN BEL_MN NuevaOPC_16_MN PVM TasaGPI CR_calc RC_calc CO_calc);
set D2021.rescap_2021_08 (RENAME=(pe_2021_08=PrimaEmitida));
run;
PROC SORT DATA=rescap_2021_08;
BY NoPoliza;
RUN;

data WORK.rescap_2020_08 (keep=NoPoliza statuspoliza_orig FechaEmision fechavaluacion FechaFirmaCredito PlazoMeses FrecPago PrimaEmitida MontoSolicitado MontoSolicitadoSiniestro MontoPagadoSiniestro sumaasegurada_MN BEL_MN NuevaOPC_16_MN PVM TasaGPI CR_calc RC_calc CO_calc);
set D2020.rescap_2020_08 (RENAME=(pe_2020_08=PrimaEmitida));
run;
PROC SORT DATA=rescap_2020_08;
BY NoPoliza;
RUN;

data WORK.rescap_2021_07 (keep=NoPoliza statuspoliza_orig FechaEmision fechavaluacion FechaFirmaCredito PlazoMeses FrecPago PrimaEmitida MontoSolicitado MontoSolicitadoSiniestro MontoPagadoSiniestro sumaasegurada_MN BEL_MN NuevaOPC_16_MN PVM TasaGPI CR_calc RC_calc CO_calc);
set D2021.rescap_2021_07 (RENAME=(pe_2021_07=PrimaEmitida));
run;
PROC SORT DATA=rescap_2021_07;
BY NoPoliza;
RUN;

data WORK.rescap_2021_06 (keep=NoPoliza statuspoliza_orig FechaEmision fechavaluacion FechaFirmaCredito PlazoMeses FrecPago PrimaEmitida MontoSolicitado MontoSolicitadoSiniestro MontoPagadoSiniestro sumaasegurada_MN BEL_MN NuevaOPC_16_MN PVM TasaGPI CR_calc RC_calc CO_calc);
set D2021.rescap_2021_06 (RENAME=(pe_2021_06=PrimaEmitida));
run;
PROC SORT DATA=rescap_2021_06;
BY NoPoliza;
RUN;

/*UNE BASES FINALES*/

DATA WORK.VERTICAL;
SET rescap_2021_08 rescap_2021_08 rescap_2021_07;
BY NoPoliza;
RUN;

PROC SORT DATA=VERTICAL;
BY NoPoliza;
RUN;

But when i try this macro, i cant see the results

%macro bdPrimasVertical;

%do yr=2019 %to 2021;
%do ms=1 %to 12;

data earf.rescapi_&yr._&ms.
(keep=NoPoliza statuspoliza_orig FechaEmision fechavaluacion FechaFirmaCredito
PlazoMeses FrecPago PrimaEmitida MontoSolicitado MontoSolicitadoSiniestro
MontoPagadoSiniestro sumaasegurada_MN BEL_MN NuevaOPC_16_MN PVM TasaGPI
CR_calc RC_calc CO_calc);
set D&yr..rescap_&yr._&ms. (RENAME=(pe_&yr_&ms.=PrimaEmitida));
run;

PROC SORT DATA=rescapi_&yr._&ms.;
BY NoPoliza;
RUN;

%end;
%end;
%mend;

this is the log of the macro:

1 The SAS System 09:36 Monday, September 13, 2021

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Programa';
4 %LET _CLIENTPROCESSFLOWNAME='Flujo del proceso';
5 %LET _CLIENTPROJECTPATH='C:\Users\CRE-EARF\Documents\Cierres\RespaldoCierre09092021.egp';
6 %LET _CLIENTPROJECTPATHHOST='510-CRE-EARF';
7 %LET _CLIENTPROJECTNAME='RespaldoCierre09092021.egp';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=PNG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 FILENAME EGSR TEMP;
15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16 STYLE=HtmlBlue
17 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
18 NOGTITLE
19 NOGFOOTNOTE
20 GPATH=&sasworklocation
SYMBOLGEN: Macro variable SASWORKLOCATION resolves to "/work/SAS_work443E000071AA_sas-desa/SAS_work68B8000071AA_sas-desa/"
21 ENCODING=UTF8
22 options(rolap="on")
23 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24
25 GOPTIONS ACCESSIBLE;
26
27 %macro bdPrimasVertical;
28
29 %do yr=2019 %to 2021;
30 %do ms=1 %to 12;
31
32 data earf.rescapi_&yr._&ms.
33 (keep=NoPoliza statuspoliza_orig FechaEmision fechavaluacion FechaFirmaCredito
34 PlazoMeses FrecPago PrimaEmitida MontoSolicitado MontoSolicitadoSiniestro
35 MontoPagadoSiniestro sumaasegurada_MN BEL_MN NuevaOPC_16_MN PVM TasaGPI
36 CR_calc RC_calc CO_calc);
37 set D&yr..rescap_&yr._&ms. (RENAME=(pe_&yr_&ms.=PrimaEmitida));
38 run;
39
40 PROC SORT DATA=rescapi_&yr._&ms.;
41 BY NoPoliza;
42 RUN;
43
44 %end;
45 %end;
46 %mend;
47
48 GOPTIONS NOACCESSIBLE;
49 %LET _CLIENTTASKLABEL=;
50 %LET _CLIENTPROCESSFLOWNAME=;
51 %LET _CLIENTPROJECTPATH=;
52 %LET _CLIENTPROJECTPATHHOST=;
53 %LET _CLIENTPROJECTNAME=;
54 %LET _SASPROGRAMFILE=;
55 %LET _SASPROGRAMFILEHOST=;
56
2 The SAS System 09:36 Monday, September 13, 2021

57 ;*';*";*/;quit;run;
58 ODS _ALL_ CLOSE;
59
60
61 QUIT; RUN;
62

Re: macro loop in data set

Reeza — Mon, 13 Sep 2021 17:26:48 GMT

%macro bdPrimasVertical;

%do yr=2019 %to 2021;
%do ms=1 %to 12;

data rescapi_&yr._&ms.
(keep=NoPoliza statuspoliza_orig FechaEmision fechavaluacion FechaFirmaCredito
PlazoMeses FrecPago PrimaEmitida MontoSolicitado MontoSolicitadoSiniestro
MontoPagadoSiniestro sumaasegurada_MN BEL_MN NuevaOPC_16_MN PVM TasaGPI
CR_calc RC_calc CO_calc);

set D&yr..rescap_&yr._&ms. (RENAME=(pe_&yr_&ms.=PrimaEmitida));
run;

PROC SORT DATA=rescapi_&yr._&ms.;
BY NoPoliza;
RUN;

%end;
%end;
%mend;

%bdPrimasVertical;

Your code looks correct to me, in the latest post - except for the small fact that you never run the macro. The macro is a set of stored code that you need to explicitly execute, so adding the last line may be all you need. You did have the double period issue.

FYI- you may want to consider an alternative - stacking all your data sets into one and using BY group processing. How big are each of these data sets?

Something like this to get started and then adding in your drop/keep as needed.

data rescap_2021;
set D2021.rescap_2021_:  indsname=source;

yearMonth = source;
primaEmitida = coalesce(of pe_2021_:);

run;

Re: macro loop in data set

ERI_RAMIREZ — Mon, 13 Sep 2021 18:01:27 GMT

Thank you very much I almost done, i have one final question how can I PADL the variable ms=1, i want the macro read the data base as in this example:

rescap_01_2021 instead of rescap_1_2021

Re: macro loop in data set

Tom — Mon, 13 Sep 2021 18:19:23 GMT

@ERI_RAMIREZ wrote:

Thank you very much I almost done, i have one final question how can I PADL the variable ms=1, i want the macro read the data base as in this example:

rescap_01_2021 instead of rescap_1_2021

If you are using a %DO loop to increment a macro variable as if it was a number, like this:

%do month=1 %to 12;

But you need the value to have leading zeros then use the PUTN() function with the Z format to add the leading zeros.

%do year=2020 %to 2021 ;
  %do month=1 %to 12;
    %let month=%sysfunc(putn(&month,Z2.));
    %let dsname=rescap_&month._&year;
...