DATA Step, Macro, Functions and more

How to calculate mean in a data set and populate all the remaining varaibles to the mean OBS

Reply
Super Contributor
Posts: 272

How to calculate mean in a data set and populate all the remaining varaibles to the mean OBS

Dear,

 

In my data the following varaible present. I calculated mean first separating data and transpoing data and merging the dataset set  with main dataset. It was a multistep process, but I am not able to populate the remaining variable values to the OBS  containing mean value OBS. Please help.

 

data

 

id                           test              date                                            value             country

1                           sbp             2015-06-18T06:00                         100                usa

1                           sbp             2015-06-18T06:30                         102                usa

1                            sbp             2015-06-18T07:00                        104                 usa

1                           dbp             2015-06-18T06:00                         90                usa

1                           dbp             2015-06-18T06:30                         92                usa 

1                           dbp             2015-06-18T07:00                        94                 usa

 

ouput needed:

id                           test              date                                            value             country

1                           sbp             2015-06-18T06:00                         100                usa

1                           sbp             2015-06-18T06:30                         102                usa

1                            sbp            2015-06-18T07:00                        104                 usa

1                     sbpavg              2015-06-18                                   102                usa

1                           dbp             2015-06-18T06:00                         90                usa

1                           dbp             2015-06-18T06:30                         92                usa 

1                           dbp             2015-06-18T07:00                        94                 usa

 

1                    dbpavg              2015-06-18                                   92                usa

Valued Guide
Posts: 797

Re: How to calculate mean in a data set and populate all the remaining varaibles to the mean OBS

If you are making a dataset, instead of generating a report, then please realize you can't have the variable DATE sometime contain datetime values and sometimes contain date values.  So what this program does is break it up into two variables: DAT and TIM.  For the "average" row, set TIM to  missing.

 

Assuming dataset have is sorted by datetime (variable DATE) within ID/TEST.

 

Note the VNEED data set is a data set VIEW (not a data set FILE), which means it is only created on demand, (i.e. when it is accessed in a subsequent SET, or  MERGE, statement).  The consequence is that it is never written to disk, but simply "piped" to the calling SET statement.  Save a lot of disk space and disk input/output when dealing with large data sets - at the cost of some memory. 

 

data vneed/view=vneed;
  set have;
  dat=datepart(date);  format dat yymmddn8.;
  tim=timepart(date);  format tim time8.0;
  drop date;
run;

data want (drop=n vsum);
  set vneed;
  by id test dat notsorted;
  vsum+value;
  n+1;
  if first.dat then do;
    n=1; vsum=value;
  end;
  output;
  if last.dat then do;
    test=cats(test,'avg');
    tim=.;
    value=vsum/n;
    output;
  end;
run;

 

 

Ask a Question
Discussion stats
  • 1 reply
  • 123 views
  • 1 like
  • 2 in conversation