thank you! this seems to fill in averages with nearest before and after, but how can I adjust this to only fill in values if the values it pulls from are within 7 days? thanks!
OK. That would be more simple.
data have;
input date : anydtdte. zip temp;
format date date9.;
datalines;
2000jan1 90001 50
2000jan2 90001 51
2000jan3 90001 53
2000jan4 90001 .
2000jan5 90001 49
2000jan6 90001 .
2000jan7 90001 .
2000jan8 90001 .
2000jan9 90001 50
2000jan10 90001 55
;
run;
data want;
if _n_=1 then do;
if 0 then set have;
declare hash h(dataset:'have',hashexp:20);
h.definekey('zip','date');
h.definedata('temp');
h.definedone();
end;
set have;
if missing(temp) then do;
do i=1 to 7;
_date=date-i;
call missing(temp);
if h.find(key:zip,key:_date)=0 and not missing(temp) then do;lag=temp;leave;end;
end;
do i=1 to 7;
_date=date+i;
call missing(temp);
if h.find(key:zip,key:_date)=0 and not missing(temp) then do;next=temp;leave;end;
end;
temp=(lag+next)/2;
end;
format _date date9.;
drop lag next i _date;
run;
'call missing(temp);'
Due to TEMP is in Hash Table ,so I set it missing before invoking Hash Table, to avoid to retain the lag value of TEMP.
'if h.find(key:zip,key:_date)=0 and not missing(temp) then do;lag=temp;leave;end;'
Invoke Hash Table ,according to key variables zip and _date to find TEMP value,
if TEMP is not missing then TEMP is what I want ,and set its value to lag , and leave the whole loop,because of finding what I want and no need to loop the next ones.
great! this is perfect. thank you so much. what would you suggest is the best way to run this same loop for other variables? im creating a macro variable for this - do you agree?
You can just replace variable TEMP with the variable you need .
or rename your name as temp.
like:
data have;
set your-table(rename=(your-variable=temp));
run;
If you are trying to make macro, make sure use double quote around macro variable.
like in Hash Table:
h.definedata('temp');
should be
h.definedata(" ¯o_variable ");
data have;
input date $ zip temp;
datalines;
jan1 90001 50
jan2 90001 51
jan3 90001 53
jan4 90001 .
jan5 90001 49
jan6 90001 .
jan7 90001 .
jan8 90001 .
jan9 90001 50
jan10 90001 55
;
run;
data want;
drop _: rc;
if _N_ = 1 then do;
declare hash h();
rc = h.defineKey('zip','__n');
rc = h.defineData('temp');
rc = h.defineDone();
end;
do _n=1 by 1 until(last.zip);
set have;
by zip;
if _n>1 and not missing(temp) and lag(temp)=. then
do;
_avg=mean(temp,_temp);
__temp=temp;
temp=_avg;
rc=h.check();
if rc=0 then h.replace();
do __n=__n by -1 while(rc=0);
rc=h.check();
if rc=0 then h.replace();
end;
temp=__temp;
end;
if not missing(temp) then _temp=temp;
else if missing(temp) then
do;
__n=_n;
h.add();
end;
end;
do __n=1 by 1 until(last.zip);
set have;
by zip;
rc=h.find();
output;
end;
run;
/*Part B*/
@pamplemouse22 I'm afraid I require more and clearer explanation on the 7 day logic fitment for me to tweak the code at-least for my learning as by no means my solutions are worth moving to production as I believe quality of mine is poor 😞
Anyway here is my question, I am dividing your HAVE by 7 to see the weeks as groups
data have1;
input date :date9. zip temp;
if lag(zip) ne zip then k=0;
k=ceil(date/7); /*notice here*/
format date date9.;
datalines;
--Your datalines follow--
;
Okay, The week starting 8Jan to 14jan for 9002 has missing imputations for the dates 13 and 14 in your example. Isn't that part of the valid 7 days and requires to be imputed? and likewise for others My English seems awful. Pardon me for my poor comprehension.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: