Help in Proc sql code

[knveraraju91]

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

[art297]

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

[knveraraju91]

Thank you.

How to write code for  param values for each calculated aval values. 

[art297]

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