topic Need help using this macro - 24727 - Robust macro to write a delimited file- Sample in SAS Programming

Need help using this macro - 24727 - Robust macro to write a delimited file- Sample

chimen1 — Fri, 28 Sep 2012 13:49:28 GMT

How do I change this base code to remove the leading and trailing spaces on the header PLUS use null values instead of a space for missing values. example:

ACCOUNT_NUMBER | ADDRESS_FILTER_DATE | PHONE_FILTER_DATE |

W2 153502|0612|0809| | | | | | |4042414252|0909| | | |

Re: Need help using this macro - 24727 - Robust macro to write a delimited file- Sample

ballardw — Fri, 28 Sep 2012 14:26:45 GMT

It would help to know what code generated the output if you are asking how to modify the code.

Re: Need help using this macro - 24727 - Robust macro to write a delimited file- Sample

chimen1 — Fri, 28 Sep 2012 14:48:08 GMT

I am so sorry - I used base code found in SAS examples called "24727 - Robust macro to write a delimited file- Sample"

actual code is:

OPTIONS SOURCE DQUOTE MISSING='';

DATA READIT;

INFILE READIT MISSOVER;

INPUT @ 0001 ACCOUNT_NUMBER $CHAR22.

@ 0023 ADDRESS_FILTER_DATE $CHAR04.

@ 0027 PHONE_FILTER_DATE $CHAR04.

@ 0031 COA_NAME $CHAR27.

@ 0058 COA_ADDRESS $CHAR39.

@ 0097 COA_CITY $CHAR15.

@ 0112 COA_STATE $CHAR02.

@ 0114 COA_ZIP $CHAR09.

@ 0123 COA_LAST_UPDT_DATE $CHAR04.

@ 0127 PHONE_01 $CHAR10.

@ 0137 PHONE_01_UPDT_DATE $CHAR04.

@ 0141 PHONE_02 $CHAR10.

@ 0151 PHONE_02_UPDT_DATE $CHAR04.

@ 0155 PHONE_03 $CHAR10.

@ 0165 PHONE_03_UPDT_DATE $CHAR04.;

/* NO CHANGES HERE - YET ANYWAY - GO FURTHER DOWN.*/

%MACRO MAKEFILE

(

DATASET=_LAST_ , /* DATASET TO WRITE */

FILENAME=WRITEIT , /* FILE TO WRITE TO */

DLMR="," , /* DELIMITER BETWEEN VALUES */

QTES="NO" , /* SHOULD SAS QUOTE ALL CHARACTER VARIABLES? */

HEADER="YES" , /* DO YOU WANT A HEADER LINE W/ COLUMN NAMES? */

LABEL="NO" /* SHOULD LABELS BE USED INSTEAD OF VAR NAMES? */

);

PROC CONTENTS DATA=&DATASET OUT=___OUT_;

/* RETURN TO ORIG ORDER */

PROC SORT DATA=___OUT_; BY VARNUM;

/* BUILD LIST OF VARIABLE NAMES */

DATA _NULL_;

SET ___OUT_ NOBS=COUNT;

CALL SYMPUT("NAME"!!LEFT(_N_),TRIM(LEFT(NAME)));

CALL SYMPUT("TYPE"!!LEFT(_N_),TRIM(LEFT(TYPE)));

/* USE VAR NAME WHEN LABEL NOT PRESENT */

IF LABEL=" " THEN LABEL=NAME;

CALL SYMPUT("LBL"!!LEFT(_N_),TRIM(LEFT(LABEL)));

IF _N_=1 THEN CALL SYMPUT("NUMVARS", TRIM(LEFT(PUT(COUNT, BEST.))));

/* CREATE FILE */

DATA _NULL_;

SET &DATASET;

FILE &FILENAME;

%GLOBAL TEMP;

%IF &QTES="YES" %THEN %LET TEMP='"';

%ELSE %LET TEMP=; /* OVER-RIDE - WAS A SPACE ' ' */

%IF &HEADER="YES" %THEN %DO;

/* CONDITIONALLY ADD COLUMN NAMES */

IF _N_=1 THEN DO;

PUT %IF &LABEL="YES" %THEN %DO;

%DO I=1 %TO &NUMVARS-1;

&TEMP "%TRIM(%BQUOTE(&&LBL&I)) " +(-1) &TEMP &DLMR

%END;

&TEMP "%TRIM(%BQUOTE(&&LBL&NUMVARS)) " &TEMP;

%END;

%ELSE %DO;

%DO I=1 %TO &NUMVARS-1;

&TEMP "%TRIM(&&NAME&I) " +(-1) &TEMP &DLMR

%END;

&TEMP "%TRIM(&&NAME&NUMVARS) " &TEMP ;

%END;

;

END;

%END;

/* BUILD PUT STMT TO WRITE VALUES */

PUT

%DO I = 1 %TO &NUMVARS -1;

%IF &&TYPE&I NE 1 AND &QTES="YES" %THEN %DO;

'"' &&NAME&I +(-1) '"' &DLMR

%END;

%ELSE %DO;

&&NAME&I +(-1) &DLMR

%END;

%IF &&TYPE&I NE 1 AND &QTES="YES" %THEN %DO;

/* WRITE LAST VARNAME */

'"' &&NAME&NUMVARS +(-1) '"';

%END;

%ELSE %DO;

/* WRITE LAST VARNAME */

&&NAME&NUMVARS;

%END;

RUN;

%MEND MAKEFILE;

/* NOW INVOKE THE MACRO! */

/* CHANGES ARE TO BE MADE HERE AS NEEDED. */

/* MAKE SURE THAT THE VARIABLES ARE IN THE ORDER YOU WANT */

/* AND ALSO IN THE DESIRED FORMAT. */

%MAKEFILE(DATASET=READIT, /* SAS DATASETNAME NAME */

FILENAME=WRITEIT, /* DDNAME OF THE FILE WRITTEN TO */

DLMR="|", /* LETS USE A PIPE */

QTES="NO",

HEADER="YES", /* YEP - USE VARIABLE NAMES. */

LABEL="NO"); /* NOPE - WILL USE VARIABLE NAMES */

RUN;

Re: Need help using this macro - 24727 - Robust macro to write a delimited file- Sample

Cynthia_sas — Fri, 28 Sep 2012 14:59:17 GMT

Hi:

If you use ODS CSV, you can change the delimiter that's used to write out the output file. Use:

ODS CSV file='c:\temp\somefile.csv' options(doc='Help');

proc print data=sashelp.class;

run;

ODS CSV close;

and then look in the log at the Help info to find the name of the suboption to change the delimiter. I'm on a machine without SAS right now, so I can't check.

Otherwise, if you have issues with macro code contained in a Tech Support note, then you probably should either not change the macro code until you understand what it's doing or work with Tech Support to change it so you don't break anything.

cynthia

Re: Need help using this macro - 24727 - Robust macro to write a delimited file- Sample

MikeZdeb — Fri, 28 Sep 2012 15:08:42 GMT

Hi ... fyi, sometimes when you find sample code on the SAS web site, it's good to check the date it was posted. Example, under the ABOUT tab at ...

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

Type: Sample

Topic: SAS Reference ==> DATA Step

Common Programming Tasks ==> Reading and Writing External Data

SAS Reference ==> Macro

Date Modified: 2009-12-24 09:07:45

Date Created: 2004-09-30 14:09:08

So, that was originally posted in 2004 ... a lot has changed within SAS in eight years. The code still works, but as with a lot of older examples, there are other (newer) ways to accomplish the same task (as Cynthia points out in this case ... ODS) that take advantage of newer features in SAS.