BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
EduardoQuiroz
Obsidian | Level 7

Hola ,Estoy en busca de como  realizar una consulta de una tabla con base en una macro  que proviene de otra tabla , esto Ya lo he logrado , sin embargo  quiero que esta se repita N# de Veces  Teniendo encuenta  que al termino de una consulta  se sume N =N+1  y vuelva a comenzar una parte del programa  desde que comienza el Do hasta End     Gracias

Data;
%LET N=0;
%LET O=1;
%LET N=%eval(&N+&O);


do while(&n < 5);

PROC SQL;
SELECT
    PUT(CTA_CVE,19.) INTO:CLIENTE 
From WORK.CTA_MES_L Where ROW_NUM =&N
;QUIT;
%PUT &CLIENTE;


PROC SQL;
SELECT
    (CTA_FCH_PRM_CMP) INTO:FCH_PRM_CMP
From WORK.CTA_MES_L Where ROW_NUM =&N
;QUIT;
%PUT &FCH_PRM_CMP;


PROC SQL;
Create table TXN as
Select 
FCH   Format   DATE9.   Length   8,
INF   Format   6.   Length   8,
CVE   Format   19.   Length   8,
TRN_CVE   Format   6.   Length   8,
PLAN_CVE   Format   11.   Length   8,
TRN_IMP   Format   20.2   Length   8
From  TABLE.VFAC_TR
where FCH_FCH = "&FCH_PRM_CMP"D
And CTA_CVE=&CLIENTE;
QUIT;


PROC SQL; /* */
INSERT INTO NFCONTRO.ACUM    
SELECT * FROM TXN ;
RUN;

%end;

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26
proc sql;
    create table txn as select
          put(a.cta_cve,19.) as cliente,
          a.cta_fch_prm_cmp as fch_prm_cmp,
          b.fch,
          b.inf,
          b.cve
/* More variables can go here if you want */
    from cta_mes_l as a left join vfac_t as b
         on b.fch_fch=a.fch_prm_cmp
             and b.cta_cve=put(a.cta_cve,19.);
quit;
     

This is completely untested because I don't have your data sets, and so there may be some modifications needed, but this will do what you want without looping and without macros.

 

From now on, when you think about using macros and loops, stop and ask yourself (and ask others in this forum or other who you work with) if macros are need and if loops are needed. Macros are incredibly inefficient here, and take a lot longer to program properly.

--
Paige Miller

View solution in original post

5 REPLIES 5
PaigeMiller
Diamond | Level 26

Please explain (in words, not in SAS code) what this sequence of SQL with macro variables is trying to do. It seems as if you are extracting one row from a data set in one SQL to get a date value, then extracting the same row from another data set in the next SQL to get a client, and then doing a third operation where you extract data from another data set by date and client.

 

None of this requires a macro, none of this requires a loop, if I am understanding your code, however I am guessing. Please take the time to explain what it is doing.

 

 

--
Paige Miller
EduardoQuiroz
Obsidian | Level 7
Hello What I am trying to do is extract from a database that I have my rows numbered from 1 to 70000 I want to extract the client's account and the date of the same account where my row number is number 1, I have already achieved this with my macro , however in the next step I use the information stored in the macro and I am going to look for information with these characteristics in another table , so far my code works having found that N = 1
%LETN=0;
%LET O=1;
%LET N=%eval(&N+&O);


Now, the next step is to create a loop that repeats this part of the code but now with row 2, which in this case would be N = 1 +1 = 2 and so on until reaching the number 70000

And to store this information I create a table which I insert all the values I found for N =1 and below N= 2….3…..5……70000
PaigeMiller
Diamond | Level 26

None of this requires a loop or a macro.

 

Extract from each database all 70000 desired records into a data set. Using PROC SQL, join the two data base extracts and the data in VFAC_TR.

--
Paige Miller
PaigeMiller
Diamond | Level 26
proc sql;
    create table txn as select
          put(a.cta_cve,19.) as cliente,
          a.cta_fch_prm_cmp as fch_prm_cmp,
          b.fch,
          b.inf,
          b.cve
/* More variables can go here if you want */
    from cta_mes_l as a left join vfac_t as b
         on b.fch_fch=a.fch_prm_cmp
             and b.cta_cve=put(a.cta_cve,19.);
quit;
     

This is completely untested because I don't have your data sets, and so there may be some modifications needed, but this will do what you want without looping and without macros.

 

From now on, when you think about using macros and loops, stop and ask yourself (and ask others in this forum or other who you work with) if macros are need and if loops are needed. Macros are incredibly inefficient here, and take a lot longer to program properly.

--
Paige Miller
adrianet86
Fluorite | Level 6

Saludos estimado, si resolviste tu problema de los ciclos? Yo tengo algunos ejemplos que realizamos para cuando generamos información retroactiva por años y requerimos información por meses, igual podría servirte, quedo a la orden 😉