BookmarkSubscribeRSS Feed
Michael_B-T
Calcite | Level 5

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:sasexample1.png

 

5 REPLIES 5
Reeza
Super User

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:sasexample1.png

 


 

ballardw
Super User

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.

PGStats
Opal | Level 21

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;
PG
Michael_B-T
Calcite | Level 5

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

 

PGStats
Opal | Level 21

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;
PG

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 726 views
  • 0 likes
  • 4 in conversation