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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.