BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
pamplemouse22
Calcite | Level 5

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!

Ksharp
Super User

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;
pamplemouse22
Calcite | Level 5
This works! Thank you so much. I have not used hash before - seems like a very efficient solution.

Can you please explain what the following line does?
Thanks!

call missing(temp);
if h.find(key:zip,key:_date)=0 and not missing(temp) then do;lag=temp;leave;end;
Ksharp
Super User

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

pamplemouse22
Calcite | Level 5

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? 

Ksharp
Super User

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(" &macro_variable ");

 

novinosrin
Tourmaline | Level 20

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.

 

 

 

 

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
  • 21 replies
  • 4951 views
  • 1 like
  • 7 in conversation