BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
nicnad
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

5 REPLIES 5
PGStats
Opal | Level 21

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
nicnad
Fluorite | Level 6

Thank you both for your quick replies. Works great.

esjackso
Quartz | Level 8

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
Fluorite | Level 6

@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
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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