How to create a function in PROC FCMP using information from a dataset?

[LilianaRomero]

I have a dataset similar to

Var1	Var2	Date1	Date2	Date3
A	1	1/1/2019	1/16/2020	1/30/2021
B	2	6/15/2018	6/30/2019	7/14/2020
C	2	5/26/2018	6/10/2019	6/24/2020

And I need to create a function that assigns a value depending on the values of a second dataset like the following

Var2	Year	Var3
1	2018	1000
1	2019	2000
1	2020	3000
1	2021	4000
2	2018	5000
2	2019	6000
2	2020	7000
2	2021	8000

The main idea is that if Var1 = A then the function assign the value of Var3 according to Var2 and the year of Date1, if Var1 = B then the function assigns the value in Var3 according to Var2 and the year of Date2 and if Var1 = C then the function assign the value in Var3 according to Var2 and the year of Date3. In order to get the following result:

Var1	Var2	Date1	Date2	Date3	Var3
A	1	1/1/2019	1/16/2020	1/30/2021	2000
B	2	6/15/2018	6/30/2019	7/14/2020	6000
C	2	5/26/2018	6/10/2019	6/24/2020	7000

Thanks a lot for your help!

[ballardw]

No need for Proc FCMP, and strong suspicion that if you could get FCMP to work the code is messy.

This is variation on any sort of look up. "Trick" add a variable with the one date you need to the data set.

 data one;
   input Var1 $	Var2	Date1 :mmddyy10.	Date2  :mmddyy10.	Date3  :mmddyy10. ;
   format date1 date2 date3 mmddyy10.;
   select (var1);
      when('A') targetdate=date1;
      when('B') targetdate=date2;
      when('C') targetdate=date3;
   end;
datalines;
A	1	1/1/2019	1/16/2020	1/30/2021
B	2	6/15/2018	6/30/2019	7/14/2020
C	2	5/26/2018	6/10/2019	6/24/2020
;

data two;
  input Var2	Year	Var3;
datalines;
1	2018	1000
1	2019	2000
1	2020	3000
1	2021	4000
2	2018	5000
2	2019	6000
2	2020	7000
2	2021	8000
;

proc sql; 
   create table want as
   select one.var1, one.var2, one.date1, one.date2, one.date3
          ,two.var3
   from one
        left join
        two 
        on one.var2=two.var2 and year(one.targetdate)=two.year
  ;
quit; 

Proc SQL is designed to have all sorts of tools for combining data on complex conditions. The repeated use of "one" and "two" in the Proc SQL step is that identifies the data set so one.var1 means use var1 from set one.

If your data is in a library other than work you would create an alias to use a single identifier for the sets like:

 "from sashelp.class as a" and use a.variable to reference those as the Proc won't allow use of "sashelp.class.variable".

Note how the example data is presented as a data step. That is the preferred way to provide data so we can test code. Test the data step before submitting then on the forum open a text box using the </> and paste the code. The text box is important because the main message windows on this forum will reformat pasted text often changing data step code so that it may not run.

Another approach that might work would be to use code similar to the Select in Data one to create a year variable and then sort and merge the two sets on Var2 and year. However that is more likely to have issues in some cases.

[andreas_lds]

You could use a hash-object:

data work.want;
    set work.one;
    
    if 0 then set work.two;
    
    if _n_ = 1 then do;
        declare hash h(dataset: 'work.two');
        h.defineKey('Var2', 'Year');
        h.defineData('Var3');
        h.defineDone();
    end;
    
    select (Var1);
        when ('A') year = year(date1);
        when ('B') year = year(date2);
        when ('C') year = year(date3);
    end;
    
    h.find();
run;

[BillM_SAS · Accepted solution]

To answer the original question of how to do this in PROC FCMP, I borrowed the data sets from ballardw and the hash code from andreas_lds. The hash code in this SAS program is a little different because in FCMP that hash object has some differences from the DATA step hash object.

data input_1;
   input Var1 $	Var2	Date1 :mmddyy10.	Date2  :mmddyy10.	Date3  :mmddyy10. ;
   format date1 date2 date3 mmddyy10.;
   select (var1);
      when('A') targetdate=date1;
      when('B') targetdate=date2;
      when('C') targetdate=date3;
   end;
datalines;
A	1	1/1/2019	1/16/2020	1/30/2021
B	2	6/15/2018	6/30/2019	7/14/2020
C	2	5/26/2018	6/10/2019	6/24/2020
;
run;

data input_2;
  input Var2	Year	Var3;
datalines;
1	2018	1000
1	2019	2000
1	2020	3000
1	2021	4000
2	2018	5000
2	2019	6000
2	2020	7000
2	2021	8000
;
run;

OPTION CMPLIB=sasuser.funcs;

proc fcmp OUTLIB=sasuser.funcs.example;
   function getVar3(var2, targetDate);
      declare hash h(dataset: 'work.input_2');
	  rc = h.defineKey('var2', 'year');
      rc = h.defineData('var3');
	  rc = h.defineDone();

      year = YEAR(targetDate);
	  rc = h.find();

      return(var3);
	  endsub;
	quit;

data result(drop=targetdate);
  set input_1;
  var3=getVar3(var2, targetdate);
  run;

proc print data=work.result; run;

[LilianaRomero]

Thanks, this works perfectly