table macro now fails in SAS 9.3 / convert numeric to char / proc printo

[michael_friendly]

This is a somewhat complex question, and I hope someone in this forum can help me resolve it without filing a query to support@sas.com.

For my book, Visualzing Categorical Data, I wrote a macro, table, to handle common requirements to collapse a frequency table over

some factor(s) and/or to apply sas formats to numeric variables so that they could be labeled appropriately in plots.

This was needed particularly in plots produced using SAS/IML which reads variables as either numeric or character and cannot apply

formats to numeric variables as in SAS procedures.

The macro can be found at

doc: http://www.datavis.ca/sasmac/table.html

source: http://www.datavis.ca/sas/vcd/macros/table.sas

I've recently been informed by a user of my book that the macro fails to work when the char=Y option is specified under SAS 9.3,

whereas it did work under SAS 9.2.  The code I used in the macro to convert numeric variables to character relies on using PROC PRINTTO

to write the formatted data to a temporary file and then read it back in in a separate data step.  Something changed between SAS 9.2

and SAS 9.3 regarding PROC PRINTTO, but I haven't been able to figure out what.  

But, perhaps there is another way to accomplish what I want-- to convert numeric variables to formatted character variables

in another dataset with the same variable names.  When I wrote this macro, the only way to do this I knew of was to use

PROC PRINTTO, but I always thought this was a kludge because you could not change a variable from numeric to character

in a data step.

Demonstration code (using attached data file, berkeley.sas)

*include catdata(berkeley);

%include 'path/to/berkeley.sas';

*include macros(table);

%include 'path/to/table.sas'

*-- Collapse over dept;

%table(data=berkeley, out=berk2, var=Admit Gender, weight=freq,

    char=Y, format=admit admit. gender $sex.,

    order=data);

Log file from SAS 9.2, where it works:

24   *-- Collapse over dept;

25   %table(data=berkeley, out=berk2, var=Admit Gender, weight=freq,

26       char=Y, format=admit admit. gender $sex.,

27       order=data);

NOTE: There were 24 observations read from the data set WORK.BERKELEY.

NOTE: The data set WORK.BERK2 has 4 observations and 3 variables.

NOTE: There were 4 observations read from the data set WORK.BERK2.

NOTE: The infile TABLE is:

      Filename=c:\temp\table.out,

      RECFM=V,LRECL=120,File Size (bytes)=175,

      Last Modified=17Feb2012:11:23:59,

      Create Time=17Feb2012:11:10:11

NOTE: 8 records were read from the infile TABLE.

      The minimum record length was 0.

      The maximum record length was 27.

NOTE: The data set WORK.BERK2 has 4 observations and 3 variables.

Log file from SAS 9.3, where it fails:

80    *-- Collapse over dept;

81    %table(data=berkeley, out=berk2, var=Admit Gender, weight=freq,

82        char=Y, format=admit admit. gender $sex.,

83        order=data);

NOTE: There were 24 observations read from the data set WORK.BERKELEY.

NOTE: The data set WORK.BERK2 has 4 observations and 3 variables.

NOTE: There were 4 observations read from the data set WORK.BERK2.

NOTE: The infile TABLE is:

      Filename=c:\temp\table.out,

      RECFM=V,LRECL=120,File Size (bytes)=0,

      Last Modified=17Feb2012:11:20:21,

      Create Time=17Feb2012:11:10:11

NOTE: 0 records were read from the infile TABLE.

NOTE: The data set WORK.BERK2 has 0 observations and 3 variables.

[Astounding]

Michael,

It would be more helpful if you would turn on MPRINT.  The log would be more meaningful.

In the meantime, consider that you may need to change this comparison in the future:

%if &sysver > 6.10 %then %do;

Because of the decimal point, this will make a character comparison.  Once you hit version 10 of SAS, the comparison will be false.

Good luck.

[michael_friendly]

I haven't been able to make any progress on this problem.  Something has changed with PROC PRINTO in version 9.3,

such that my use of this method to convert selected numeric variables to character no longer works.  Below is a log file

using the same macro (except that I changed the

%if &sysver > 6.10 %then %do; to %if &sysver >=7 %then %do;)

It seems that the line in the macro,

proc printto new print=table;

does not create the file with the formatted values.

The suggestion by data_null_ to use ODS LISTING CLOSE didn't work either.  By default, in SAS 9.3 (Win XP),

ODS LISTING is off anyway.  To me, this seems like a bug in PROC PRINTTO under SAS 9.3.

1    %include catdata(berkeley);

NOTE: Format ADMIT has been output.

NOTE: Format YN has been output.

NOTE: Format DEPT has been output.

NOTE: SAS went to a new line when INPUT statement reached past the end of a

      line.

NOTE: The data set WORK.BERKELEY has 24 observations and 4 variables.

23

24   options mprint;

25   *-- Collapse over dept;

26   %table(data=berkeley, out=berk2, var=Admit Gender, weight=freq,

27       char=Y, format=admit admit. gender $sex.,

28       order=data);

MPRINT(TABLE):   proc freq data=berkeley order=data ;

MPRINT(TABLE):   weight freq;

MPRINT(TABLE):   tables Admit * Gender / noprint sparse out=berk2(drop=percent);

MPRINT(TABLE):   format admit admit. gender $sex.;

MPRINT(TABLE):   run;

NOTE: Writing HTML Body file: sashtml.htm

NOTE: There were 24 observations read from the data set WORK.BERKELEY.

NOTE: The data set WORK.BERK2 has 4 observations and 3 variables.

MPRINT(TEMPFILE):   filename table "c:\temp\table.out" lrecl=120;

MPRINT(TABLE):  ;

MPRINT(TABLE):   proc printto new print=table;

MPRINT(TABLE):   options nodate nocenter nonumber ls=120 ps=10000;

MPRINT(TABLE):   proc print data=berk2;

MPRINT(TABLE):   id Admit Gender;

MPRINT(TABLE):   var count;

MPRINT(TABLE):   run;

NOTE: There were 4 observations read from the data set WORK.BERK2.

MPRINT(TABLE):   proc printto print=print;

MPRINT(TABLE):   data berk2;

MPRINT(TABLE):   infile table length=len;

MPRINT(TABLE):   length string $120 Admit Gender $16;

MPRINT(TABLE):   retain skipping 1;

MPRINT(TABLE):   drop string skipping;

MPRINT(TABLE):   input @1 string $varying. len @;

MPRINT(TABLE):   if skipping=0 & string ^= ' ' then do;

MPRINT(TABLE):   input @1 Admit $ Gender $ count;

MPRINT(TABLE):   output;

MPRINT(TABLE):   end;

MPRINT(TABLE):   else input;

MPRINT(TABLE):   if index(string,'COUNT')>0 then skipping=0;

MPRINT(TABLE):   run;

NOTE: The infile TABLE is:

      Filename=c:\temp\table.out,

      RECFM=V,LRECL=120,File Size (bytes)=0,

      Last Modified=28Feb2012:12:15:49,

      Create Time=17Feb2012:11:10:11

NOTE: 0 records were read from the infile TABLE.

NOTE: The data set WORK.BERK2 has 0 observations and 3 variables.

MPRINT(TABLE):   *proc contents data=&out;

MPRINT(TEMPDEL):   *-- Avoid annoying flash with X commands;

MPRINT(TABLE):  ;

MPRINT(TABLE):  ;

MPRINT(TABLE):   options notes LS=80 PS=60 DATE CENTER;

[data_null__]

My suggestion was that ODS LISTING was already closed and that it should be turned on.  ODS LISTING;

My example was to show the with it CLOSEd there would be no ERROR just as you reported.

Another approach would be to use ODS LISTING FILE=...; in place of PROC PRINTTO. 

[Astounding]

Michael,

Help us out here.  When this program finishes, what is in c:\temp\table.out?  Is it actually empty?  If you apply data_null_;'s suggestion, does that change?

Also note, changing the &sysver comparison to 7 may not be enough to overcome the decimal point problem.  It is possible that &sysver will contain a decimal point.  In macro language, this is still a character comparison and will be false:

%if 10.0 > 7 %then %do;

It might be easier just to remove the %IF comparison, and add a comment about what to change if you are using an archaic version of SAS.

[ArtC]

The following test worked for me:

proc format ;
value gender
0='Male'
1='Female';
run;

data havenum;
sex=1; date=today();output;
format sex gender. date date9.;
run;
filename table 'c:\temp\table.out' lrecl=120;
ods listing;
proc printto print=table new;
proc print data=havenum;
run;
proc printto;
run;

Notice that, like DATA _NULL_ suggested, the LISTING destination MUST be turned on before the PRINTTO can write to the PRINT location.  The LISTING destination is by default turned off for SAS9.3.

The fix for Astounding's comment for detecting versions is to use a %SYSEVALF to surround the expression.

%if %sysevalf(&sysver > 6.10) %then %do;

[data_null__]

Could it be as simple as ODS LISTING CLOSE;

To be more specific in this example ODS LISTING is closed but the PRINTTO bits still work in that there are no errors.  Just no data.

ods html file='test.htm';
ods listing close;
filename FT75F001 temp;
proc printto new print=FT75F001;
   run;
proc print data=sashelp.class;
   run;
proc printto;
   run;
data _null_;
   infile FT75F001;
   input;
   put _infile_;
   run;
ods html close;

With regards to the conversion of numeric to character with format consider using VVALUE or PROC TRANPOSE.  I like proc tranpose when I have a lot of variables to convert.

Message was edited by: data _null_

[ArtC]

There is a DATA step solution for converting numeric to Character in my macro book chapt 6 question 4.  Down load the sample code and look for the answer (search the sample code for :

***A1.6.4

***%ALLCHAR