BookmarkSubscribeRSS Feed
Demographer
Pyrite | Level 9

Hi, 

Pop2 is a dataset with individuals and their characteristics. lfp is a dataset including parameter from logit regression. For every individual in Pop2, I want to merge parameters corresponding to his characteristics. I have this code in SQL. It works, but it's a bit long and I'd like to reduce it with a macro (it would be also easier to use it if I change the logit model). I'm not really sure what should I do, because the merge is based on different number of variables. 

	proc sql;
	  create table pop_lfp1 as
	    select 
	      p.*,
	      t1.intercept,
	      t2.edu_p,
	      t3.agegr_p,
	      t4.agegr_edu_p,
	      t5.young_kid_p,
	      t6.region_p
	     
	    from 
	      pop2 p
	
	      left join
	      (
	        select sex, intercept
	        from param.lfp
	        where not missing(intercept)
	      ) t1
	      on p.sex=t1.sex
	      
	      left join
	      (
	        select sex, edu, edu_p
	        from param.lfp
	        where not missing(edu_p)
	      )t2
	      on p.sex=t2.sex and p.edu=t2.edu
	      
	      left join
	      (
	        select sex, agegr, agegr_p
	        from param.lfp
	        where not missing(agegr_p)
	      )t3
	      on p.sex=t3.sex and p.agegr=t3.agegr
	      
	      left join
	      (
	        select sex, agegr, edu, agegr_edu_p
	        from param.lfp
	        where not missing(agegr_edu_p)
	      )t4
	      on p.sex=t4.sex and p.agegr=t4.agegr and p.edu=t4.edu
	
	      left join
	      (
	        select sex, young_kid, young_kid_p
	        from param.lfp
	        where not missing(young_kid_p)
	      )t5
	      on p.sex=t5.sex and p.young_kid=t5.young_kid
	
	      left join
	      (
	        select sex, region, region_p
	        from param.lfp
	        where not missing(region_p)
	      )t6
	      on p.sex=t6.sex and p.region=t6.region
		    ;
	quit;
9 REPLIES 9
Kurt_Bremser
Super User

You can use multiple hash objects:

data pop_lfp1;
set pop2;
if 0 then set lfp;
if _n_ = 1
then do;
  declare hash h_itc (dataset:"lfp (keep=sex intercept where=(intercept ne .))");
  h_itc.definekey("sex");
  h_itc.definedata("intercept");
  h_itc.definedone();
  declare hash h_edu (dataset:"lfp (keep=sex edu edu_p where=(edu_p ne .))");
  h_edu.definekey("sex","edu");
  h_edu.definedata("edu_p");
  h_edu.definedone();
end;
rc = h_itc.find();
rc = h_edu.find();
drop rc;
run;

I only added two lookups, the others are done in a similar way.

PaigeMiller
Diamond | Level 26

As I look at your example, I don't see any potential benefit from macros here. Macros are useful in many ways, one of which is that a macro can eliminate the need for repetitive coding. However each of your joins is not repetitive, there's something different about each join. So I'm not seeing this as an example where macros would eliminate much work, in fact coding a macro for this situation might be more work than the actual non-macro coding that you show.

--
Paige Miller
Demographer
Pyrite | Level 9

The codes following the left join are pretty much the same everywhere. Only the number and the name of variables change. The point of doing a macro would be to use it the merge other dataset with a similar structure (but different variables).

Tom
Super User Tom
Super User

Why not just combine ALL of the data?  Then to change the model your just have to change the model.

Demographer
Pyrite | Level 9

That what I wanted to do. Is there any easier way to combine data?

Patrick
Opal | Level 21

@Demographer 

Does below give you what you're after?

%macro hashLookUp(ds, keyVars, valVar);
  %global h_cnt;
  %let h_cnt=%eval(&h_cnt+1);

  %local _keyVars;
  %let _keyVars=%unquote("%qsysfunc(prxchange(s/\b\s+\b/%nrbquote(",")/oi,-1,&keyVars))");

  if _n_=1 then
    do;
      if 0 then set &ds (keep=&keyVars &valVar);
      declare hash h&h_cnt. (dataset:"&ds (keep=&keyVars &valVar where=(not missing(&valVar)))", hashexp:4);
      h&h_cnt..definekey(&_keyVars);
      h&h_cnt..definedata("&valVar");
      h&h_cnt..definedone();
    end;
  call missing(&valVar);
  _rc=h&h_cnt..find();
  drop _rc;
%mend;

data want;
  set pop2;
  %hashLookUp(lfp, sex, intercept)
  %hashLookUp(lfp, sex edu, edu_p)
  %hashLookUp(lfp, sex agegr, agegr_p)
  %hashLookUp(lfp, sex young_kid, young_kid_p)
  %hashLookUp(lfp, sex region, region_p)
run;

 

Tom
Super User Tom
Super User

Why do you want to combine the logistic output with the data?

Are you attempting to use the model to predict a response?

Can't you do that already in the step that created the output? Most of SAS regresssion type procedure will let you include observations with missing dependent variable and have it produce predicted values for those cases?

Or use PROC SCORE to score the new data?

Demographer
Pyrite | Level 9
Yes, I want to predict a response. The logit model however comes from different survey. Can I still use proc score in that case?
Tom
Super User Tom
Super User

@Demographer wrote:
Yes, I want to predict a response. The logit model however comes from different survey. Can I still use proc score in that case?

You should be able to.  See this documentation page.  https://support.sas.com/documentation/cdl/en/statug/63033/HTML/default/viewer.htm#statug_logistic_se...

Here is example in documentation:

https://documentation.sas.com/?docsetId=statug&docsetTarget=statug_logistic_examples20.htm&docsetVer...

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 9 replies
  • 1713 views
  • 4 likes
  • 5 in conversation