Proc freq help

[nicnad]

Hi,

Lets say I have the following table :

ApplProvenance	MontantOperation	MontantBilletEspece
CO	11000	35000
CO	20000	100
DI	5000	500
IN	100	450
IN	45000	50000

How do I proc freq a table like that :

ApplProvenance	MontantOperation < 10000	MontantOperation >=10000	MontantBilletEspece < 10000	MontantBilletEspece >= 10000
CO	0	2	1	1
DI	1	0	1	0
IN	1	1	1	1

I tried the following code, but I am not able to make it work :

proc format;

value montantoperation low-9999='MontantOperation <10000$'

    1000-high='MontantOperation >=10000$';

run;

proc format;

value montantbilletespece low-9999='MontantBilletEspece <10000$'

    1000-high='MontantBilletEspece >=10000$';

run;

proc freq data = sasuser.impw_0026 ;

tables applprovenance montantoperation montantbilletespece / out=test2;

format montantoperation montantbilletespece;

run;

Thank you for your help and time.

[PGStats · Accepted solution]

You could get very close to what you want with proc tabulate...

data have;
input ApplProvenance $ MontantOperation MontantBilletEspece;
datalines;
CO 11000 35000
CO 20000 100
DI 5000 500
IN 100 450
IN 45000 50000
;

proc format;
value montantoperation low-<10000 = 'MontantOperation <10000$'
    10000-high='MontantOperation >=10000$';
value montantbilletespece low-<10000='MontantBilletEspece <10000$'
    10000-high='MontantBilletEspece >=10000$';
run;

proc tabulate data=have format=13.;
class ApplProvenance MontantOperation MontantBilletEspece;
table ApplProvenance="", (MontantOperation="" MontantBilletEspece="")*n="";
format MontantOperation MontantOperation. MontantBilletEspece MontantBilletEspece.;
run;

PG

[nicnad]

Thank you both for your quick replies. Works great.

[esjackso]

You can always recode the variables and use SQL to produce the report:

data have2;

  set have;

  monoperl=0; monoperg=0; monbilll=0; monbillg=0;

  if MontantOperation < 10000 then monoperl = 1;

  else monoperg = 1;

   if MontantBilletEspece < 10000 then monbilll = 1;

  else monbillg = 1;

run;

proc sql ;

  select applprovenance,

  sum(monoperl) as monoperl label= 'MontantOperation <10000',

  sum(monoperg) as monoperg label= 'MontantOperation >=10000',

  sum(monbilll) as monbilll label= 'MontantBilletEspece <10000',

  sum(monbillg) as monbillg label= 'MontantBilletEspece >=10000'

  from have2

  group by applprovenance

  ;

quit;

[nicnad]

@esjackso Lets say I would have a forth column with the year of the transaction, how can, in SQL, group by applprovenance and by year so that I would have those category :

CO_2007

CO_2008

IN_2007

IN_2008

DI_2007

etc...

Thank you for your help and time.

[esjackso]

The easiest would be to add the year in the select and the group by statements:

proc sql ;

  select applprovenance, YEAR format=whatever sas date format you want,

  sum(monoperl) as monoperl label= 'MontantOperation <10000',

  sum(monoperg) as monoperg label= 'MontantOperation >=10000',

  sum(monbilll) as monbilll label= 'MontantBilletEspece <10000',

  sum(monbillg) as monbillg label= 'MontantBilletEspece >=10000'

  from have2

  group by applprovenance, year

  ;

quit;

This doesnt create a new variable but adds the year column to the report. If you need the applprovenance and year in the same variable you could combine the two in a new varaible in the datastep: newvar = catx('_',applprovenance,year); You would then replace applprovenance (two times) with the new variable in SQL.

Hope this helps!

EJ