Dear,
Some one already helped me in my code.
Link https://communities.sas.com/t5/Base-SAS-Programming/Help-in-Proc-sql-code/m-p/460346#M117003
In order to get the output i need to do proc transpose after proc sql step. After the proc transpose step i had to do a data step to rename the variable name "NAME OF FORMER VARIABLE" to "Param" and I had to delete col 2 to col 4. Is there anything i can do to modify my sql code to get the output i need without additional transpose and data step. Thank you
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.,
count(id) as numberofrecords,
intck('week',min(datepart(calculated exstdt)),
max(datepart(calculated exstdt)),'c') as durationinweeks,
calculated numberofrecords/(round(calculated durationinweeks/3)+1) as compliance
from one
group by id,trt
;
QUIT;
proc transpose data=two out=three;
by id trt;
var numberofrecords durationinweeks compliance;
run;
output needed:
id trt param aval 1 A numberofrecords 4 1 A durartioninweeks 9 1 A compliance 1 1 B numberofrecords 4 1 B durartioninweeks 9 1 B compliance 1 2 A numberofrecords 4 2 A durartioninweeks 9 2 A compliance 1 2 B numberofrecords 4 2 B durartioninweeks 9 2 B compliance 1
... View more
