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
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;
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.
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.