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:
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:
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:
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.
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;
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
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: