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

I have a dataset similar to

Var1Var2Date1Date2Date3
A11/1/20191/16/20201/30/2021
B26/15/20186/30/20197/14/2020
C25/26/20186/10/20196/24/2020


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

Var2YearVar3
120181000
120192000
120203000
120214000
220185000
220196000
220207000
220218000



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:

Var1Var2Date1Date2Date3Var3
A11/1/20191/16/20201/30/20212000
B26/15/20186/30/20197/14/20206000
C25/26/20186/10/20196/24/20207000


Thanks a lot for your help!

1 ACCEPTED SOLUTION

Accepted Solutions
BillM_SAS
SAS Employee

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;

View solution in original post

4 REPLIES 4
ballardw
Super User

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
Jade | Level 19

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
SAS Employee

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

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!
What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 547 views
  • 4 likes
  • 4 in conversation