Help with a simple ods excel graph: Date time and a variable Free MSU

[Arju1]

Hello

I need to create a graph of time on x-asis and temperature on y-axis. But my time variables are dependent on date. My Data is:

Date             Time             Free MSU

14/11/2020   7:02:00.000     24
14/11/2020   7:07:00.000     24
14/11/2020   7:12:00.000     25
14/11/2020   7:17:00.000     -9
14/11/2020   7:22:00.000     23

15/11/2020   6:57:00.000     46
15/11/2020   7:02:00.000     48
15/11/2020   7:07:00.000     146
15/11/2020   7:12:00.000     146
15/11/2020   7:17:00.000     147

I am using following SAS statements:

data work.msudatax;
infile 'd:\sasdata\charts\msudata.csv'
delimiter=','
missover
firstobs=2
DSD
lrecl = 32767
obs=290;
input Date:ddmmyy10. Time:time. FreeMSU TotalMSU;
format Date ddmmyy10. Time time8.7;
run;
proc Contents data=work.msudatax;
run;
ods excel file="E:\QXG\temp.xlsx" options(sheet_name="gugus.xls");
proc print data=work.msudatax noobs;
title "MSU Report";
ods excel options(sheet_interval = 'proc' sheet_name = "MSU");
ods graphics / reset width = 6.4in height=4.8in imagemap;
proc sgplot data = work.msudatax ;
needle x=Time y=FreeMSU / group=Date;
run;
ods graphics / reset;
ods excel close;

SAS  plots it but it plots as in the attached file. I need the time on x-axis starting from 7:02 of 14th till the time 7:17 of 15th.

Thank you for the help.

[jarapoch · Accepted solution]

Hello, Arju1.

I understood you want to create a needle graph with date time in xaxis. If I was right, I'd create a datetime variable (date_time) with a data step.

data msudatax; 
	set msudatax;
	date_time=dhms(date,0,0,time);
run;

and then I'd change proc sgplot for this one:

proc sgplot data = work.msudatax ;
	needle x=date_time y=FreeMSU;
	format date_time datetime14. ;
	xaxis type= discrete display=(nolabel) fitpolicy=SPLIT splitchar=':';
run;

This graph would be display

I attach the full code.

data work.msudatax;
infile datalines delimiter=',' missover firstobs=1 DSD;
input Date :ddmmyy10. Time:time. FreeMSU;
datalines;
14/11/2020,7:02:00.000,24
14/11/2020,7:07:00.000,24
14/11/2020,7:12:00.000,25
14/11/2020,7:17:00.000,-9
14/11/2020,7:22:00.000,23
15/11/2020,6:57:00.000,46
15/11/2020,7:02:00.000,48
15/11/2020,7:07:00.000,146
15/11/2020,7:12:00.000,146
15/11/2020,7:17:00.000,147
;
run;
data msudatax; 
	set msudatax;
	date_time=dhms(date,0,0,time);
run;

proc Contents data=work.msudatax;
run;

ods excel file="\temp.xlsx" options(sheet_name="gugus.xls");
	proc print data=work.msudatax (drop=date_time) noobs;
	title "MSU Report";
ods excel options(sheet_interval = 'proc' sheet_name = "MSU");

ods graphics / reset width = 6.4in height=4.8in imagemap;
proc sgplot data = work.msudatax ;
	needle x=date_time y=FreeMSU;
	format date_time datetime14. ;
	xaxis  type= discrete display=(nolabel) fitpolicy=SPLIT splitchar=':';
run;
ods graphics / reset;
ods excel close;

[Arju1]

Thank you very much. Works perfectly. Appreciate the help.