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!
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;
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.
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;
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;
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 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: