Help using Base SAS procedures

Proc freq help

Accepted Solution Solved
Reply
Regular Contributor
Posts: 186
Accepted Solution

Proc freq help

Hi,

Lets say I have the following table :

ApplProvenanceMontantOperationMontantBilletEspece
CO1100035000
CO20000100
DI5000500
IN100450
IN4500050000

How do I proc freq a table like that :

ApplProvenanceMontantOperation < 10000MontantOperation >=10000
MontantBilletEspece < 10000
MontantBilletEspece >= 10000
CO0211
DI1010
IN1111

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.


Accepted Solutions
Solution
‎03-26-2013 08:29 PM
Respected Advisor
Posts: 4,747

Re: Proc freq help

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

PG

View solution in original post


All Replies
Solution
‎03-26-2013 08:29 PM
Respected Advisor
Posts: 4,747

Re: Proc freq help

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

PG
Regular Contributor
Posts: 186

Re: Proc freq help

Thank you both for your quick replies. Works great.

Super Contributor
Posts: 333

Re: Proc freq help

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;

Regular Contributor
Posts: 186

Re: Proc freq help

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

Super Contributor
Posts: 333

Re: Proc freq help

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 260 views
  • 3 likes
  • 3 in conversation