topic Re: How to depict quartals of the year on the x-axis in SAS Programming

How to depict quarters of the year on the x-axis

Anita_n — Tue, 05 Apr 2022 09:53:04 GMT

Dear all,

I have a data which looks like this

phase	QI_2021	QII_2021	QIII_2021	QI_2022
I	5	10	14	8
II	20	30	66	55
III	60	70	78	67

QI_2021 represents the months January, February and March (the first quarter 2021)

QII_2021 represents the months April, May and June (the second quarter 2021 )

QIII_2021 represents the months July, August and September (the third quarter 2021)

I wish to make a plot which looks similar to this

The quarters should be on the x-axis. I was thinking of assigning the months of the year on the xaxis, my problem is how to let the line start from within jan, feb, march region for the first quarter (for example) which in this case will be QI_2021. Maybe someone has a good tip for me how I can do that.

Re: How to depict quartals of the year on the x-axis

Kurt_Bremser — Tue, 05 Apr 2022 09:29:02 GMT

Maxim 19: Long Beats Wide. Do not keep data (timepoints) in structure (variable names).

So we first create a transposed dataset with date values:

data have;
infile datalines dlm="09"x;
input phase $ QI_2021 QII_2021 QIII_2021 QI_2022;
datalines;
I	5	10	14	8
II	20	30	66	55
III	60	70	78	67
;

proc transpose
  data=have
  out=long1 (rename=(col1=patients))
;
by phase;
var Q:;
run;

data long2;
set long1;
year = input(scan(_name_,2,"_"),4.);
select (substr(scan(_name_,1,"_"),2));
  when ("I") m = 1;
  when ("II") m = 4;
  when ("III") m = 7;
  when ("IV") m = 10;
end;
qtr = mdy(m,1,year);
format qtr yyq7.;
drop _name_ m year;
run;

The graphics procedures can then make use of the data in a quite simple way:

ods graphics / reset width=6.4in height=4.8in imagemap;

title "Patients Phase I-III";

proc sgplot data=long2;
label
  patients = "Patients"
  qtr = "Quarter"
;
vline qtr / response=patients group=phase;
yaxis grid;
run;

ods graphics / reset;
title "";

The code was developed with the Diagram task in SAS Studio and then refined with a title.

Result:

Re: How to depict quartals of the year on the x-axis

Anita_n — Wed, 06 Apr 2022 08:40:33 GMT

@Kurt_Bremser is it also possible to change the format of 2021Q1 to Q12021.

I tried several ways but it isn't working.

I tried using the format qyy7.; (this doesn't work at all)
Used proc format this way:

proc format;
value qt '01jan2021'= "Q12021"
              '01apr2021'= "Q22021"
              '01jul2021'=   "Q32021"
              '01oct2021'= "Q42021"
              '01jan2022'= "Q12022"
;
run;

when I plot the graph, only the first and last values a printed out correctly the other values show something like this 22371, 22462, 22554

Re: How to depict quartals of the year on the x-axis

Kurt_Bremser — Wed, 06 Apr 2022 09:29:02 GMT

Roll your own picture format to display dates like this:

proc format;
picture myqtr (default=6)
  low-high='%q%Y' (datatype=date)
;
run;

But I ran into problems using this format in the SGPLOT procedure. I posted a new question dealing with this: https://communities.sas.com/t5/Graphics-Programming/Why-does-SGPLOT-not-use-a-custom-format-correctly/m-p/806228

Re: How to depict quartals of the year on the x-axis

Anita_n — Wed, 06 Apr 2022 09:41:42 GMT

I had already tried that also, unfortunately it doesn't work either. I get something like %q%Y on the graph.

Re: How to depict quartals of the year on the x-axis

Kurt_Bremser — Wed, 06 Apr 2022 09:45:37 GMT

Yep, that's exactly the problem I ran into, and that's why I posted my question. I am honestly baffled, and hope that someone can explain to us what's going on there. Right now, I actually consider this a bug in SGPLOT.

Re: How to depict quartals of the year on the x-axis

Anita_n — Wed, 06 Apr 2022 09:55:19 GMT

Okay, thanks for that. I hope someone else can help!!

Re: How to depict quartals of the year on the x-axis

Kurt_Bremser — Wed, 06 Apr 2022 10:02:01 GMT

I found it. The Graph Template Language (the "code" of the graphics fed to ODS which then makes pictures out of it) cannot dealt with the directives of the picture format for dates, times and datetimes. See here and here.

But you can work around this by creating a new variable, and forcing the order in the graph based on the order in the dataset:

data long2;
set long1;
year = input(scan(_name_,2,"_"),4.);
select (substr(scan(_name_,1,"_"),2));
  when ("I") m = 1;
  when ("II") m = 4;
  when ("III") m = 7;
  when ("IV") m = 10;
end;
qtr = mdy(m,1,year);
format qtr yyq7.;
qtr1 = put(qtr,myqtr.);
drop _name_ m year;
run;

proc sgplot data=long2;
label
  patients = "Patients"
  qtr1 = "Quarter"
;
vline qtr1 / response=patients group=phase;
yaxis grid;
xaxis discreteorder=data;
run;

Re: How to depict quartals of the year on the x-axis

Anita_n — Wed, 06 Apr 2022 10:40:52 GMT

I tried that, this is what I get in the log:


47   data long2;
48   set long1;
49   year = input(scan(_name_,2,"_"),4.);
50   select (substr(scan(_name_,1,"_"),2));
51     when ("I") m = 1;
52     when ("II") m = 4;
53     when ("III") m = 7;
54     when ("IV") m = 10;
55   end;
56   qtr = mdy(m,1,year);
57   format qtr yyq7.;
58   qtr1 = put(qtr,myqtr.);
                    ------
                    48
ERROR 48-59: The format MYQTR was not found or could not be
             loaded.

59   drop _name_ m year;
60   run;

NOTE: The SAS System stopped processing this step because of
      errors.
WARNING: The data set WORK.LONG2 may be incomplete.  When this
         step was stopped there were 0 observations and 4
         variables.
WARNING: Data set WORK.LONG2 was not replaced because this step
         was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

Re: How to depict quartals of the year on the x-axis

Kurt_Bremser — Wed, 06 Apr 2022 10:44:40 GMT

Since I did not specify a permanent location in my PROC FORMAT code, you always have to run that code before you use the format. The format will be stored in a catalog in WORK and not be available in a new session.

Re: How to depict quartals of the year on the x-axis

Anita_n — Wed, 06 Apr 2022 11:56:15 GMT

Yes, you are right. It was my mistake. It worked the only thing I added was a Q at the beginning of the high-low value to get the Q's at the front of results, like getting Q12021 instead of 12021. Thank you very much

see code:

proc format;
picture myqtr (default=6)
  low-high='Q%q%Y' (datatype=date)
;
run;