topic Re: 2 way table with a twist- help! in SAS Programming

2 way table with a twist- help!

Michael_B-T — Thu, 18 Oct 2018 15:31:55 GMT

Hello all,

I'm a new SAS user.

I'm looking to replicate a SQL crosstab query in SAS.

The query in question has columns which are days of the month (from 1 - 31) whilst the columns are of months and corresponding values within.

Ive tried using my date values and proc tabulate but without many positive results.

Any tips?

Many many thanks!

Please see the SQL access example below:

Re: 2 way table with a twist- help!

Reeza — Thu, 18 Oct 2018 15:48:03 GMT

Hi, welcome to the SAS Forums.

Unfortunately there is not enough information in your post to answer your question.

Is that what you have, or what you want? You need to provide:

what you have to start with
what you want as the output
and, preferably, what you've tried so far

Then someone should be able to help you answer your question. It may also help to provide the previous SQL query. SAS supports SQL so depending on how complex of a query it is, you may just be able to wrap it in PROC SQL statements. However SAS does not support Pivots or Windowing functions.

@Michael_B-T wrote:

Hello all,

I'm a new SAS user.

I'm looking to replicate a SQL crosstab query in SAS.

The query in question has columns which are days of the month (from 1 - 31) whilst the columns are of months and corresponding values within.

Ive tried using my date values and proc tabulate but without many positive results.

Any tips?

Many many thanks!

Please see the SQL access example below:

Re: 2 way table with a twist- help!

ballardw — Thu, 18 Oct 2018 17:40:02 GMT

If you have a working SQL code to make a crosstab it may, depending on what version you have, be convertible into SAS PROC SQL code. May. The idea behind crosstabs is pretty much contrary to normalized data that SQL is designed for.

Since you don't provide any example input data, any variable names or types or any rules for what gets "crosstabbed" not much detail can be provided.

Re: 2 way table with a twist- help!

PGStats — Thu, 18 Oct 2018 17:54:02 GMT

Looks like all you need is to transpose some data. An example:

data test;
call streaminit(65651);
do date = '01JAN2017'd to '31DEC2017'd;
    if weekday(date) in (1, 7) then call missing(value);
    else value = rand("Poisson", 100);
    month = intnx("month", date, 0);
    day = day(date);
    output;
    end;
format month mmyys7.;
run;

proc transpose data=test out=want(drop=_name_) prefix=day_;
by month;
id day;
var value;
run;

Re: 2 way table with a twist- help!

Michael_B-T — Mon, 22 Oct 2018 13:55:07 GMT

HI PGStats

Thanks for that!

Very helpful!

Playing around with the code I would like to embed instead of the random values some values from another table in correspondence to the dates.

This is what I tried

data test;
set PART2;
do sesdate_DATE = '01JAN2018'd to '31DEC2018'd;
    if weekday(sesdate_DATE) in (1, 7) then call missing(enratt);
	else value = enratt;
    month = intnx("month", sesdate_DATE, 0);
    day = day(sesdate_DATE);
    output;
    end;
format month mmyys7.;
run;

With "enratt" being the name of the column with the actual values and sesdate_DATE actual dates.

The problem with the above is that I receive lots of 0s and . instead of the actual values from the table called Part2 in the Value column.

Any tips?

Many thanks

Re: 2 way table with a twist- help!

PGStats — Mon, 22 Oct 2018 17:31:04 GMT

To use your own data, you only need to add month and day, and make sure the data is sorted:

data test;
set PART2;
value = enratt;
month = intnx("month", sesdate_DATE, 0);
day = day(sesdate_DATE);
format month mmyys7.;
run;

proc sort data=test; by month day; run;