topic Re: Proc freq help in SAS Procedures

Proc freq help

nicnad — Tue, 26 Mar 2013 22:30:24 GMT

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

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.

Re: Proc freq help

PGStats — Wed, 27 Mar 2013 00:29:05 GMT

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;

Re: Proc freq help

esjackso — Wed, 27 Mar 2013 12:51:58 GMT

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;

Re: Proc freq help

nicnad — Wed, 27 Mar 2013 14:37:48 GMT

Thank you both for your quick replies. Works great.

Re: Proc freq help

nicnad — Wed, 27 Mar 2013 15:00:28 GMT

@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.

Re: Proc freq help

esjackso — Wed, 27 Mar 2013 15:22:31 GMT

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!