/** Quote comma output macro. Charles Burchill MCHPE - Feb. 21, 1996. Usage: _lotus ; Options: input - Input data set name. input=SAS_dataset _name output - [required] This is the name of the quote comma seperated output file. It should be defined as a filename earlier in the program or a full name in quotes. output="/home/cpe/burchil/output.txt" comment - Put this line as the first line in the output file. This string must be in quotes. comment='This is an output file' Caution: Do not use the '=' symbol as part of the comment string. var - List of variables to save in dataset. This string must be in quotes (e.g. var='region age sex'. Default is to save all variables into the file. Variables are saved to the file in the order listed. type - Type of output. comma, qc, or tab. comma and qc will output a quote comma seperated file tab will output a tab seperated file. qc is the default. This type of output will maintain compatability with the previous version (no formated numerics) and can be correctly imported into quattro pro. The comma type will use the formated values of numeric variables. Note that character strings will not be quoted and may not import correctly. label - Use available variable names or labels for column titles. Options - label, name. Default name. Beware of long labels, they will fill the 256 column limit very quickly. label=name lrecl - logical record length. If you want your output to be longer Than 256 columns you must specify the logical record length. lrecl=356 Excel will read files wider than 256 characters. delim - Define your own delimter. Must be in quotes. delim='$' delim='09'x (this is tab delimiter). debug - turn on mprint and notes. debug=debug Note: The option does not have to be defined. Putting the word 'debug' as a parameter is enough. addfile - Add the filename as second comment line. This only works on UNIX systems with the SAS_SYSIN_INFO variable defined. addfile=yes ; Note: The option does not have to be defined. Putting the word 'debug' as a parameter is enough. Note: The following messages are not errors: The type=qc option generates a format called _MISSIT on the fly to convert missings to " ". WARNING: Format _MISSIT is already on the library. If there are no numeric, or character variables then the following warning is issued. This warning may also be generated when the specified variables do not exist on the data set. WARNING: Defining an array with zero elements. **/ %macro _lotus(input= , output= , comment= , var= , type=qc, debug= , label=name, lrecl=256, delim= , addfile= ) /stmt des='Output delimited files' ; %put Easy Quote Comma, and Tab Output for SAS Datasets ; %put Charles A. Burchill, Manitoba Centre for Health Policy and Evaluation ; %put Input from D. Friesn, Shelley Derksen, Ruth Bond and others ; %put $Id: _lotus.mac,v 3.8 1997/10/06 19:59:29 burchil Exp burchil $ ; %* Initial Parameters and checks ; %if &output = %then %goto out ; %let nobs = 0 ; %if &debug = 1 %then %let debug=debug ; %if &debug ^= debug %then options nonotes nomprint ; %else options notes mprint ; ; %if &addfile = 1 %then %let addfile=yes ; %* Set input data set to last if it is missing ; %if &input = %then %let input = %trim(%substr(&sysdsn,1,8)).%trim(%substr(&sysdsn,9)) ; %* set options to a single case ; %let type=%lowcase(&type) ; %let label=%lowcase(&label) ; %* Set delimiters ; %if &delim = %then %do ; %if &type = tab %then %do ; %let delim1 = ; %let delim2 = %str('09'x) ; %let delim3 = %str('09'x) ; %end; %else %do ; %let delim1 = %str('"') ; %let delim2 = %str(',"') ; %let delim3 = %str(',') ; %end; %end ; %* If user requested a delimiter then use it ; %if &delim ^= %then %do ; %let type= ; %let delim1 = ; %let delim2 = &delim ; %let delim3 = &delim ; %end; %* Test for variables, and strip quotes if necessary ; %if &var ^= %then %do ; %let byq=%qsubstr(&var,1,1); %if &byq = %str(%') | &byq = %str(%") %then %do ; %let rq = %length(&var) ; %let var = %substr(&var,2,%eval(&rq-2)); %end; %end ; %* Count number of variables in var and test the type of variable. Then create a macro variable that contains the variable types. ; %let vtypes= ; %if &var ^= %then %do ; %let vcount = 1 ; %let word = %qscan(&var,&vcount,%str( )) ; %do %while(&word ^= ) ; %let vcount = %eval(&vcount+1) ; %let word = %qscan(&var,&vcount,%str( )) ; %end ; %let vcount = %eval(&vcount-1) ; %* Open the data and get the variable types for each listed variable ; %let dsid = %sysfunc(open(&input,i) ) ; %do i=1 %to &vcount ; %let vnum = %sysfunc(varnum(&dsid,%scan(&var,&i,%str( )))) ; %if &vnum = 0 %then %do ; %put WARNING: Variable %upcase(%scan(&var,&i,%str( ))) does not exist in data ; %let vtypes = &vtypes N ; %end ; %else %do ; %let vtypes = &vtypes %sysfunc(vartype(&dsid,&vnum)) ; %end ; %end ; %* Close the data ; %let rc=%sysfunc(close(&dsid)) ; %end ; %* Setup options for outputing missing values ; %if &type = tab | &type=comma %then %do ; options missing = ' ' ; %end ; %else %if &type = qc %then %do ; proc format ; value _missit(default=12) . = '" "' ; run; %end ; ** Set input data, keep variables and setup output ; data _null_ ; set &input %if &var ^= %then ( keep=&var ) ; end=last ; %if &var= %then %do ; * Set arrays for both character and numeric variables ; array _char_{*} _character_ ; array _num_{*} _numeric_ ; %end ; %* for qc output format the numeric variables to blanks ; %if &type=qc %then format _numeric_ _missit. ; ; * Define output data set ; file &output linesize=&lrecl lrecl=&lrecl ; %* If there is a comment the put it here ; %if &comment ^= %then if _n_ = 1 then put &comment ; ; %* If the addfile has been defined then put the name of the file ; %if &addfile = yes %then if _n_ = 1 then put "%sysget(SAS_SYSIN_INFO)" ; ; * Retain counters for total observation, and label length. If output is over this lenght then set warning flag ; retain t_lbl t_obs 0 ; * Genrate first line of variable names or labels ; if _n_ = 1 then do ; * Set length of temp var for names ; %if &label = name %then length name $8 ; %else %if &label= label %then length name $40 ; ; * Output labels or names, determine length of variables ; * Character labels first - this step is done if the var= option is not used. ; %if &var = %then %do ; do i= 1 to dim(_char_) ; %if &label=name %then call vname(_char_{i},name) ; %else %if &label=label %then call label(_char_{i},name) ; ; if i=1 then put @1 &delim1 name +(-1) &delim1 @ ; else put &delim2 name +(-1) &delim1 @ ; t_lbl = t_lbl + length(name) + 3 ; t_obs = t_obs + length(left(_char_{i})) + 3 ; end ; * Numeric labels second ; do j = ( dim(_char_) + 1 ) to (dim(_char_) + dim(_num_) ) ; %if &label = name %then call vname(_num_{j-dim(_char_)},name) ; %else %if &label=label %then call label(_num_{j-dim(_char_)},name) ; ; if j= 1 then put @1 &delim1 name +(-1) &delim1 @ ; else put &delim2 name +(-1) &delim1 @ ; t_lbl = t_lbl + length(name) + 3 ; t_obs = t_obs + length(left(_num_{j-dim(_char_)})) + 1 ; ; end ; * End first put line ; %end ; %* If the var= option is used then use the following macro based method to output the data ; %else %if &var ^= %then %do ; %do i=1 %to &vcount ; %if &label=name %then call vname(%scan(&var,&i,' '),name) ; %else %if &label=label %then call label(%scan(&var,&i,%str( )),name) ;; %if &i = 1 %then put@1 &delim1 name +(-1) &delim1 @ ; %else put &delim2 name +(-1) &delim1 @ ; ; t_lbl = t_lbl + length(name) +3 ; t_obs = t_obs + length(left(%scan(&var,&i,%str( )))) + 3 ; %end ; %end ; %* End the put statements for each datastep ; put ; * Test logical record length passed agains length of labels and obs ; * Note the -1 corrects for starting the counter above at 1 ; if (t_lbl-1) > &lrecl | (t_obs-1) > &lrecl then do ; call symput('t_lngth',left(put(t_obs-1,8.))) ; call symput('t_lbl',left(put(t_lbl-1,8.))) ; stop ; end ; end; %* If the var=option is not used then use the following code to generate output ; %if &var= %then %do ; * Output data to a file ; do i = 1 to dim(_char_) ; if i = 1 then put @1 &delim1 _char_{i} +(-1) &delim1 @ ; else PUT &delim2 _CHAR_{i} +(-1) &delim1 @ ; end ; do j = ( dim(_char_) + 1 ) to (dim(_char_) + dim(_num_) ) ; if j=1 then put @1 _num_{i} @ ; else put &delim3 _NUM_{j-dim(_char_)} +(-1) @ ; end ; %end ; %* If the var=option is used then use the following code to generate the output file ; %if &var ^= %then %do ; %do i = 1 %to &vcount ; %if &i = 1 %then %do ; %if %scan(&vtypes,&i,%str( ))=C %then put @1 &delim1 %scan(&var,&i,%str( )) +(-1) &delim1 @ ; %else put @1 %scan(&var,&i,%str( )) @ ; ; %end ; %else %do ; %if %scan(&vtypes,&i,%str( ))=C %then put &delim2 %scan(&var,&i,%str( )) +(-1) &delim1 @ ; %else put &delim3 %scan(&var,&i,%str( )) +(-1) @ ; ; %end ; %end ; %end; put ; if last then do ; call symput('t_lngth',left(put(t_obs-1,8.))) ; call symput('t_lbl',left(put(t_lbl-1,8.))) ; call symput('nobs',left(put(_n_,8.))) ; %if &var = %then call symput('vars',left(put(dim(_char_)+dim(_num_),8.))) ; ; end; run; %if &var ^= %then %let vars=&vcount ; %* Check for unknown errors ; %if %eval(&syserr>0) %then %goto out1 ; %* Check record and observation lengths ; %if &t_lngth > &lrecl | &t_lbl > &lrecl %then %goto out2 ; %* finished macro - go to exit and clean up ; %goto exit ; %* First output error ; %out: options notes ; %put WARNING: OUTPUT FILE WAS NOT DEFINED ; %PUT %STR( ) usage: _lotus input=DSN output=filename comment="comment line" ; %PUT ; %goto jumpout ; %out1: options notes ; %put WARNING: Output file may be incomplete. Check the file name ; %put %STR( ) is it a possible name. There may have been another ; %put %str( ) type of unknown error. ; %put ; %goto jumpout ; %out2: options notes ; %put WARNING: The combined length of your names/labels or observations is ; %put %str( ) over &lrecl characters. You output may not be complete. ; %put %str( ) Label length: &t_lbl ; %put %str( ) Observation length: &t_lngth ; %put ; %goto jumpout ; %exit: options notes missing='.' ; %put NOTE: OUTPUT WRITTEN TO &output ; %put %str( ) Observations in data: &nobs ; %put %str( ) Variables saved in output: &vars ; %put %str( ) Estimated observation length with delimiters: &t_lngth ; %put %str( ) Total label or name length with delimiters: &t_lbl ; %put %str( ) Logical Record Length: &lrecl ; %jumpout : options nomprint notes missing='.' ; %mend _lotus ; /***** Example of using this program **** data test ; input char $1 num test jump $ ; cards ; a 1 45 a b 2 55 b c 3 66 g ; run; proc print ; _lotus input=test var='jump num char test' output="test.out" type=tab comment='this is a test' addfile ; ******/