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.
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:
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:
@Kurt_Bremser is it also possible to change the format of 2021Q1 to Q12021.
I tried several ways but it isn't working.
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
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-correctl...
I had already tried that also, unfortunately it doesn't work either. I get something like %q%Y on the graph.
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.
Okay, thanks for that. I hope someone else can help!!
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;
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
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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: