Create a table with values dependent on other variables

[Louis_DB]

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)

[andreas_lds]

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.

[Patrick]

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;

[Louis_DB]

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

[Patrick · Accepted solution]

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;

[Louis_DB]

Sorry, report then. That is exactly what I wanted, thanks. 

[Patrick]

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.