topic Re: Create a table with values dependent on other variables in SAS Programming

Create a table with values dependent on other variables

Louis_DB — Thu, 21 Apr 2022 09:42:42 GMT

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)

Re: Create a table with values dependent on other variables

andreas_lds — Thu, 21 Apr 2022 10:55:51 GMT

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.

Re: Create a table with values dependent on other variables

Patrick — Thu, 21 Apr 2022 11:20:54 GMT

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;

Re: Create a table with values dependent on other variables

Louis_DB — Thu, 21 Apr 2022 11:24:31 GMT

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

Re: Create a table with values dependent on other variables

Patrick — Thu, 21 Apr 2022 12:17:12 GMT

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;

Re: Create a table with values dependent on other variables

Louis_DB — Thu, 21 Apr 2022 12:26:17 GMT

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

Re: Create a table with values dependent on other variables

Patrick — Thu, 21 Apr 2022 13:02:08 GMT

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.