BookmarkSubscribeRSS Feed
knveraraju91
Barite | Level 11

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

1 REPLY 1
mkeintz
PROC Star

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;

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 805 views
  • 1 like
  • 2 in conversation