group labeled variable

[Konkordanz]

Hi,

I want to group a labled variable, but it doesnt work. First I use the proc-format-step, afterwards I embed it into an sql-command. For example:

proc format;
value EC2400P
	1,2,3 		= "02 - ..."
	4 			= "03 - ..."
	5, 6, 7, 8, 10 	= "04 - ..."
	9 			= "05 - ..."
	0 			= "06 - ...";
	run;


proc sql;
create table Tab07_1 as select 
EC2400P format=EC2400P. ,
%Makro_Tab07
from test
where EC2200P is not null
group by EC2400P; 
run;

The result seems like: it uses the proc-format-condition, but it doesnt group them. Could you please help me? My Enterprise-Version: "8.3 Update 6 (8.3.6.200) (32 Bit)"

...	...
02 - ...	65.735989
02 - ...	91.410951
02 - ...	40.257713
03 - ...	1193.361815
04 - ...	45.575487
04 - ...	8.865082
04 - ...	179.951399
04 - ...	3.40302
05 - ...	10.858655
04 - ...	145.245516

[Kurt_Bremser]

SQL does not use the formatted value to build groups, you have to apply the format through a PUT function in the GROUP BY.

But you will most probably be better off doing summary statistics with PROC MEANS, which uses the formatted values for CLASS variables, but this depends on what you do in the macro_tab07 macro.

[PaigeMiller]

Agreeing with @Kurt_Bremser , leave the values as numeric (don't use PUT) and then use PROC MEANS/PROC SUMMARY. It is a better solution than using PUT in SQL, because converting numeric to character is generally unnecessary (although there are exceptions).

With PROC MEANS/PROC SUMMARY, you don't need to convert to character; and you have ways to force the categories to sort numerically, and PROC MEANS/PROC SUMMARY has all the statistical calculations that you would need, and is much more powerful and comprehensive than SQL.

[yabwon · Accepted solution]

Try like this:

/* some fake data and macro */

%macro Makro_Tab07;
count(*)
%mend;

data test;
infile cards4 DSD;
input EC2400P @@;
EC2200P=EC2400P ;
cards;
1,2,3
4
.
5, 6, 7, 8, 10
.
9
0
;
run;



proc format;
value EC2400P
	1,2,3 		= "02 - ..."
	4 			= "03 - ..."
	5, 6, 7, 8, 10 	= "04 - ..."
	9 			= "05 - ..."
	0 			= "06 - ...";
	run;


proc sql;
create table Tab07_1 as select 
put(EC2400P,EC2400P.) as EC2400P ,
%Makro_Tab07
from test
where EC2200P is not null
group by put(EC2400P,EC2400P.)
; 
QUIT; /* !!!! use QUIT to stop proc SQL. */

Just a side note, for future. "My Enterprise-Version: "8.3 Update 6 (8.3.6.200) (32 Bit)" -  SAS Enterprise Guide is just an interface to SAS computing engine, it is like Rstudio for R, Spyder for Python, or CodeBlocks for C. If you want to see which wersion of SAS you are using the best way is to 

1) run proc setinit:

proc setinit; run;

result in Log will be containing a line like "Current version: 9.04.01M8P011823"

or 

2) print macrovariable sysvlong:

%put &=sysvlong.;

with "SYSVLONG=9.04.01M8P011823" printed in the log.

All the best

bart

[Konkordanz]

Thank you all!