using Macro and loop to do proc export

Reply
Occasional Learner
Posts: 1

using Macro and loop to do proc export

Hi, i met one macro problem, hope sas professional can give me a hand...thank you so much!

 

I wang to using macro and loop to do the proc export, TEMPEX.&TABLE is a data set created in sas, it will be export as multiple txt format file, and each txt file was exported based on some criterials like &TABLE(column value or name), &BRKEEP.="NOK", &DATA_ELEMENT_PAC.txt .   

 

i didn't show the excel table i imported in sas, which is the WORK.TRANSPOSE,  I will use excel table to select multiple column value as macro variable, and then do loop and use as parameter for the proc export. at begining records and max1 run well. but macro doesn't work. BR01  to BR10  are excel table column name, and will be input into &BRKEEP, then use as a criterial to do the proc export.

 

can anyone help me!! thank you PRO.

 

my email   btgmat@gmail.com

 

 

 

 

 

 

 

DATA WORK.TRANSPOSE;

SET WORK.FOR_TRANSPOSE;

     RECORDS = _N_;

RUN;

PROC SQL;

SELECT MAX(RECORDS) AS MAX1

                INTO:MAX1   

                FROM WORK.TRANSPOSE;

QUIT;

 

%MACRO RUN01;  

%Do I=1 %to &Max1;

    PROC SQL Noprint;

                SELECT DATA_ELEMENT,DATA_ELEMENT_NAME,LEGACY,TABLE,

                       BR01,BR2,BR3,BR4,BR5,BR6,BR7,BR8,BR9,BR10

                      

                INTOSmiley Very HappyATA_ELEMENT,

                    Smiley Very HappyATA_ELEMENT_NAME,

                    :LEGACY,

                    :TABLE,

                           :BR01,

                           :BR02,

                           :BR03,

                           :BR04,

                           :BR05,

                           :BR06,

                           :BR07,

                           :BR08,

                           :BR09,

                           :BR10

 

                FROM WORK.TRANSPOSE

                Where RECORDS =&I;

     quit;

%LET BRKEEP= &BR01 &BR02 &BR03 &BR04 &BR05 &BR06 &BR07 &BR08 &BR09 &BR10;

 

  Data TEMPEX.&TABLE. (KEEP=CODENT CENTALTA CUENTNU CUENTA IDPROD CONPROD

                  FECALTA FECBAJA CDESTCTA POSITION BALANCE IMPRFAC_SIGNO LICRECTA

                  FINAL_CYCLE LICRERET  CDESTCTA NUMULTXT NUEXTPEN FEULTEXT CCLAMON

                  CONPROD_FINAL CDCONECO VATRAFIN XPOTRAMO FECINI FECFIN NUMMOVEXT

                  IMPBRUECO DESCONECO &BRKEEP.);

  SET PACB.&TABLE.;

  RUN;

 

PROC EXPORT DATA=TEMPEX.&TABLE. (WHERE=(&BRKEEP.="NOK" )) /*CARD0004 - Card Credit Limit*/

     OUTFILE='/sbg/warehouse/dq/default/pro/data01/DMP/Business_Rules/Export/CA/&DATA_ELEMENT_PAC.txt'

           DBMS=DLM

           REPLACE;

     DELIMITER='|';

RUN;

%end;

%MEND RUN01;

Grand Advisor
Posts: 17,424

Re: using Macro and loop to do proc export

Your WHERE clause doesn't make sense. 

To check it replace it with your macro variable value and see what it would resolve to. 

 

PS do you know you can export a file using a data step to create multiple text files in one datastep? Having it all in data step logic may make it easier to debug. 

http://support.sas.com/kb/24/599.html

Ask a Question
Discussion stats
  • 1 reply
  • 383 views
  • 1 like
  • 2 in conversation