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 |
|
| ||
---|---|---|---|---|---|---|
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.
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
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
Thank you both for your quick replies. Works great.
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;
@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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: