data one;
input id trt$ date $18.;
DATALINES;
1 A 2014-04-26T13:10
1 A 2014-05-17T11:30
1 A 2014-06-07T11:45
1 A 2014-06-28T13:30
1 B 2014-04-26T13:10
1 B 2014-05-17T11:30
1 B 2014-06-07T11:45
1 B 2014-06-28T13:30
2 A 2014-04-26T13:10
2 A 2014-05-17T11:30
2 A 2014-06-07T11:45
2 A 2014-06-28T13:30
2 B 2014-04-26T13:10
2 B 2014-05-17T11:30
2 B 2014-06-07T11:45
2 B 2014-06-28T13:30
;
PROC SQL;
CREATE TABLE TWO AS
SELECT id,trt,input(date,is8601dt.) as exstdt format datetime18.,((max(datepart(exstdt))-min(datepart(exstdt)))+1) /7 as aval,count(id) as ava,
group by id,trt
from one;
QUIT;
Dear,
I am trying to do some calculations in my data. I think I can do by datasteps, but I want to try proc sql. Please help. Thank you
rules;
Calculate by id,trt
param= 'numberofrecords" count number of obs by id and trt
param="duration in weeks" last date-first date+1 converted to weeks by id,trt
param="compliance" numberofrecords/{round(durationinweeks/3)+1}
I did not populate the aval values for param="duration in weeks" and "compliance" as I do not know what values I get from the code.
output needed:
id trt param aval
1 A numberofrecords 4
1 A durartioninweeks
1 A compliance
1 B numberofrecords 4
1 B durartioninweeks
1 B compliance
2 A numberofrecords 4
2 A durartioninweeks
2 A compliance
2 B numberofrecords 4
2 B durartioninweeks
2 B compliance
Your code was close. Here are a couple of needed changes:
PROC SQL;
CREATE TABLE TWO AS
SELECT id,trt,
input(date,is8601dt.) as exstdt format datetime18.,
((max(datepart(calculated exstdt))-
min(datepart(calculated exstdt)))+1) /7 as aval,
count(id) as ava
from one
group by id,trt
;
QUIT;
Art, CEO, AnalystFinder.com
Thank you.
How to write code for param values for each calculated aval values.
You'll have to check the following code to insure that the calculations are those which you actually need:
PROC SQL;
CREATE TABLE TWO AS
SELECT id,trt,
input(date,is8601dt.) as exstdt format datetime18.,
((max(datepart(calculated exstdt))-
min(datepart(calculated exstdt)))+1) /7 as aval,
count(id) as ava,
intck('week',min(datepart(calculated exstdt)),
max(datepart(calculated exstdt)),'c') as duration,
calculated ava/(round(calculated duration/3)+1) as compliance
from one
group by id,trt
;
QUIT;
Art, CEO, AnalystFinder.com
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.