Hi,
I'm trying to create a table that shows values from one column dependent on values from other columns.
Hypothetical dataset:
data have;
input MARKET_ID $ date_formatted $ labelet per_dateg_market_label;
datalines;
AQE-MAIN 11APR22 1 5
AQE-MAIN 11APR22 2 2
AQE-MAIN 11APR22 3 434
AQE-MAIN 12APR22 1 2
AQE-MAIN 12APR22 2 2
AQE-MAIN 12APR22 3 425
CPH-MAIN 11APR22 1 9
CPH-MAIN 11APR22 2 6
CPH-MAIN 11APR22 3 774
CPH-MAIN 12APR22 1 12
CPH-MAIN 12APR22 2 10
CPH-MAIN 12APR22 3 784
;
What I want to create, is a table, where on the vertical axis there would be the "MARKET_ID" followed by the 3 different "labelet" and on the horizontal axis would be the "date_formatted". The values would then be the the corresponding values from "per_dateg_market_label".
My idea was to use proc tabulate and do something like this (although not working):
proc tabulate data=work.have order=data format=comma18.2;
class MARKET_ID date_formatted;
var labelet per_dateg_market_label;
table MARKET_ID='Venue' * labelet, date_formatted=' ' * per_dateg_market_label;
run;
any help is appreciated (new to sas)
That's a report and not a table.
Below Proc Tabulate uses the sample data as posted previously (with date_formatted being a numerical variable containing a SAS Date value and with format date9. applied).
proc tabulate data=have noseps;
class market_id date_formatted labelet;
var per_dateg_market_label;
keylabel sum=' ';
table
market_id=' '*labelet=' ',
date_formatted=' '*per_dateg_market_label=' '*f=32.
;
run;
Start by restoring "date_formatted" to be a sas date again. String dates will appear in the expected order only, if the format YYYY-MM-DD is used.
Also please explain what "not working" means in your case.
Are you after a table or a report? If it's a table is below what you want?
data have;
input MARKET_ID $ date_formatted:date9. labelet per_dateg_market_label;
format date_formatted date9.;
datalines;
AQE-MAIN 11APR22 1 5
AQE-MAIN 11APR22 2 2
AQE-MAIN 11APR22 3 434
AQE-MAIN 12APR22 1 2
AQE-MAIN 12APR22 2 2
AQE-MAIN 12APR22 3 425
CPH-MAIN 11APR22 1 9
CPH-MAIN 11APR22 2 6
CPH-MAIN 11APR22 3 774
CPH-MAIN 12APR22 1 12
CPH-MAIN 12APR22 2 10
CPH-MAIN 12APR22 3 784
;
proc transpose data=have out=want(drop=_:) prefix=labelet_;
by market_id date_formatted;
id labelet;
var per_dateg_market_label;
run;
proc print data=want;
run;
Hi Patrick,
It's a table that i want (later on i want to print it as an image).
I want it to look like this:
11APR22 12APR22
AQE-MAIN 1 5 2
2 2 2
3 434 425
CPH-MAIN 1 9 12
2 6 10
3 774 784
That's a report and not a table.
Below Proc Tabulate uses the sample data as posted previously (with date_formatted being a numerical variable containing a SAS Date value and with format date9. applied).
proc tabulate data=have noseps;
class market_id date_formatted labelet;
var per_dateg_market_label;
keylabel sum=' ';
table
market_id=' '*labelet=' ',
date_formatted=' '*per_dateg_market_label=' '*f=32.
;
run;
Sorry, report then. That is exactly what I wanted, thanks.
The following certainly not the correct "scientific" definition but as a made-up "rule of thumb": A table is the object that holds your data (like the have table), a report is the object you create to surface/print this data in the way you want to look at it.
A SAS table is always rectangular.
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 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.