proc summary/means combine class/by variables unexpectedly

[Warren]

/* When I run the following PC SAS program, all the id’s are summarized into the first id, which is wrong. */

data test;

input @ 1 id 15.

@17 amt 1.

;

cards;

098022134104033 1

098022134104033 2

098022134433892 3

098022134433892 4

;

run;

proc summary data=test nway;

class id;

var amt;

output out=test1(drop=_type_ _freq_) sum=;

run;

proc print data=test1;

format id z15.;

run;

[Warren · Accepted solution]

I got the following answer from SAS Technical Support:

You need to apply a format in advance to the ID variable as PROC SUMMARY is using BEST12. behind the scenes.

Example:

data test;

      input @ 1 id 15.

              @17 amt 1.

              ;

format id z15.;

cards;

...

[ballardw]

Note that ability to assign variables to groups by use of a format is a very powerful tool in SAS. For example if you have SAS date values you can get summaries by Year, Year and Month, Month, Day of month, Day of year (1-365), Calendar quarter, Year and calendar quarter, day of the week, and week of the year by using a different format for the same variable.

And most of the analysis procedures will group values accordingly.

[Reeza]

It's a good idea to keep ID fields as character fields for this reason.