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
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 25. 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.