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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.