Need help on where to even start please

[mthomas2014]

Hello everyone.

I posted what i have to accomplish.

The part i am confused about is where do i even start with this program? And how would i start

This assignment needs to have 912 rows when it is done and right now it has 6384. I have to set it up all in one row for each week. For example each ID has 84 days and i have to have a week column and for every 7 days it should say week 1 or week 2, etc. And there is a total of 76 IDs. so 76*12=912 rows.

If this text is confusing a little. Here is an example of how it needs to be set up

(76 total)     (There is 84 days so should be 12 weeks each ID)

ID.            Week.      NumDays.   SumofDesats.    AvgDesats

1.              1.             7                

1.              2.             7

1.              3.             7

I included my code to start and the question related to what i have to do so it makes it easier. Please help

Thank you guys so much.

[Jagadishkatam]

Could you please post the sample data that is there in DesatsLong.sas7bdat for one or subject so that we can use it to write the code

[mthomas2014]

Here is the data too. sorry I forgot to include.

-Michael

[Shmuel]

1) Assuming weeks start the same date for all IDs then it is possible to create a format

    that defines the week number per date, from the earliest to the last one.

2) Please post a sample data using code like:

data have;
   infile datalines;
   input ID DAY ....;
datalines;
1  <day or date> ...
2  ..............
; run;

If you post the PROC CONTENTS output, it may be helpful too.

3) Use PROC MEANS to calculate the sum and the average per DAY assigned the new format, 

    i.e. per ID and week, creating  output dataset - one observation per ID per week.

Having a sample data enables us to post you the full code.

[Kurt_Bremser]

So you need to summarize per week, where "week" is defined not from a date value, but a day number.

See this:

proc sql;
create table want as
  select
    id,
    int(day/7) + 1 as week,
    sum(desats) as sum_desats,
    avg(desats) as avg_desats
  from desats
  group by id, calculated week
;
quit;

(untested, posted from my tablet)

[Shmuel]

In case DAY contains a date then you can use next code.

I have created a dummy test data-set:

data test;
     do ID = 1 to 5;
	    do DAY = '01JAN2020'd to '28FEB2020'd;
		   destas = 100*ranuni(0);
		   output;
		 end;
	end;
	format destas 5.2;
run;

proc sql noprint;
   select min(DAY), max(DAY) into :DayStart, :DayEnd 
     from test;
quit;
%put DayStart=&daystart  DayEnd = &dayend;

data cntl;
     retain fmtname 'week';
     do date = &DayStart to &DayEnd;
        week = int((date - &dayStart)/7 +1);
        end = date;
        label = put(week,2.);
        output;
     end;
     rename date = start;
run;
proc format lib=work cntlin=cntl; run;

proc means data=test nway noprint;
  class ID DAY;
  var destas;
  output out=want(rename=(_freq_ = DAYS) drop=_type_)
         sum=SumDestas  mean=AvgDestas;
  format day week. ;
run;

[Jagadishkatam · Accepted solution]

Please try the below code,

1) create a dummy dataset considering you have per id 12 weeks of data. in this dummy dataset you will have week and day variables.

2) merge the dummy dataset with the original dataset on day variable. this way each id will get the week information from dummy dataset. 

3) then derive the numdays, SumofDesats and AvgDesats.

data dummy;
do week= 1 to 12;
do i = 1 to 7;
day+1;
output;
end;
end;
run;

proc sort data=dummy;
by day ;
run;

data want;
merge have(in=a) dummy(in=b);
by day;
if a;
run;

proc sort data=want;
by id week;
run;

data want2;
set want;
by id week;
retain numdays SumofDesats;
if first.week then numdays=1;
else numdays+1; 
if first.week then SumofDesats=NumDesat;
else SumofDesats+NumDesat;

if last.week;
 AvgDesats=SumofDesats/numdays;
run;

[mthomas2014]

I figured it out. you guys are awesome thank you so much for the help. I cant thank you both enough.

Glad to be a part of the SAS community.

Sincerely,

Michael