BookmarkSubscribeRSS Feed
chimen1
Calcite | Level 5


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| | | |   

4 REPLIES 4
ballardw
Super User

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

chimen1
Calcite | Level 5

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;                                                                 

     %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;                                                                           

Cynthia_sas
SAS Super FREQ

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

MikeZdeb
Rhodochrosite | Level 12

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 857 views
  • 0 likes
  • 4 in conversation