- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I first created the dataset as follows:
data subdata;
set storage.data1;
date1 = input(a_dt, yymmdd10.);
date2=input(b_dt,yymmdd10.);
format date1 date2 yymmn6.;
if date1 ge "01Jan2018"d;
run;
The dataset is set up so there's a bunch of columns m01...m36 such that each column has information about a status for that given month. Each number of the month are x months after month 0. Month 0 is the month in "lastdate" below:
data testing;
set subdata;
%let lastdate = %sysfunc(inputn(202012,yymmn6.));
%let monthdiff=%sysfunc(INTCK(MONTH,date1,&lastdate));
%let t0=m&monthdiff.;
%let t1=m%eval(&monthdiff-1).;
%let t2=m%eval(&monthdiff-2).;
%let t3=m%eval(&monthdiff-3).;
%let t4=m%eval(&monthdiff-4).;
%let t5=m%eval(&monthdiff-5).;
%let t6=m%eval(&monthdiff-6).;
...
...
Check conditions for each t, etc.
For example, if "date1" is 7 months away from the "lastdate" variable, then monthdiff will equal 7, t0=m7,t1=m6, so on and so forth. Then I will check if each tx variable (which references an mx column) has a certain value and if it does, an indicator variable will generate a 1.
When I try to run the %let monthdiff=%sysfunc(INTCK(MONTH,date1,&lastdate)) line, I get the ERROR: Argument 2 to function INTCK referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number and then ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list. Execution of %SYSCALL statement or %SYSFUNC or %QSYSFUNC function reference is terminated. Date1 is the column with dates that will dynamically change the monthdiff variable, and thus each "t" variable. How can I pass that date column into the INTCK function?
I'm very new to sas. I also realize I can easily change the "%let t1, let t2, etc." lines into a macro but I ran into other issues with that.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
So there is no need for macro code for this.
You can use an array to allow you reference the value of M7 by using an index of 7 into an array of the variables named M1, M2, ....
You have not said how many M variables there are. What is the name of the two date varaibles you need to compare to calculate the target index. So here is a sketch.
data want ;
set have ;
/* How many M variables are there? */
array m m1-m20;
/* Is this the right direction? Or is it lastdate,date1 ? */
monthdiff = intck('month',date1,lastdate) ;
perf = 0;
/* MONTHDIF cannot be one or there is no way to index to monthdiff-1 */
/* MONTHDIF cannot be larger than the size of the array */
if 1 < monthdiff <= dim(m) then if m[monthdiff]='A' and m[monthdiff-1]='Z' then perf=1;
run;
You will need to make it match your dataset. Or ask more directed questions.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
This statement does not make any sense in your problem
%let monthdiff=%sysfunc(INTCK(MONTH,date1,&lastdate));
DATE1 is just a five character string to the macro processor. What actual DATE are you trying to reference here?
If it is a macro variable DATE1 (which I don't see any place where you have defined such a macro variable) then use &DATE1.
If it is the value of a variable in your dataset named DATE1 then use the function INTCK() directly in your data step instead and assign the value to a variable in your data step.
monthdiff = intck('month',date1,&lastedate);
You could then use the resulting integer as an index into an array to find the appropriate Mxxx variable.
array m m1-m12 ;
want = m[monthdiff];
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
%LET is not a sas data step statement but a declarative one to be coded out of the data step.
You should use next code instead:
data testing;
set subdata;
lastdate = input('20201201',yymmdd8.);
monthdiff= INTCK(MONTH,date1,lastdate);
t0='m' || monthdiff;
t1='m' || monthdiff-1;
t2='m' || monthdiff-2;
t3='m' || monthdiff-3;
t4='m' || monthdiff-4;
t5='m' || monthdiff-5;
t6='m' || monthdiff-6;
..........
Thus can be shorten by using an array and a do loop:
data testing;
set subdata;
lastdate = inputn(20201201,yymmdd8.);
monthdiff= INTCK(MONTH,date1,lastdate);
array tx {} T0-T6; /* or Tn where n is the last one */
do i=0 to dim(tx);
tx(i) = monthdiff - i;
end;
/***
t0='m' || monthdiff;
t1='m' || monthdiff-1;
t2='m' || monthdiff-2;
t3='m' || monthdiff-3;
t4='m' || monthdiff-4;
t5='m' || monthdiff-5;
t6='m' || monthdiff-6;
***/
If you need the Tn macro variables for future use than the right function is CALL SYMPUT as in:
do i=0 to dim(tx);
tx(i) = monthdiff - i;
call symput(vname(tx(i)) , tx(i));
end;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you! It would actually have to be
tx(i) = 'm'||monthdiff - i;
right?
And since the variable names are m1,...m36, would I just do an IF THEN statement outside the do loop as follows:
do i=0 to dim(tx);
tx(i) = 'm'|| monthdiff - i;
call symput(vname(tx(i)) , tx(i));
end;
if tx(1)='A' and tx(2)='G'...then PERF=1; else PERF=0;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I cannot make heads or tails of what you are actually trying to do. I cannot even figure out how macro variables even enter into the problem as you have described it so far.
Please post examples of the input data and explain what you are trying to do. Post the expected output for the given input.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
There are columns in my dataset, each named m1, m2, etc. etc.. Each of these columns have a value I need to check. Ultimately, I'm creating a new variable after checking some of these m1,m2, etc. columns. For example, if m1='A',m2='Z',etc. the new variable PERF=1, otherwise it equals 0.
I am not checking the same m variables for every record. The variables I check are dependent on a certain date (date1) and how far away that date is (in months) from a lastdate variable. For example, if the difference between those 2 dates are 7, then I will check m7='A',m6=Z', for 6 variables (so I will check from m7 though m1). If the difference is 10 I will check m10 though m4. So it's clearly dynamic for every observation.
The monthdiff variable is the variable I created that determines the difference in months between the two dates. My goal was to use this number as a reference to the first "m" column I would check (which I labeled as t0). Then, the column 'm'||monthdiff-1 would be the next column I would check. And so on and so forth. If all of these columns I checked matched the criteria I was looking for, then the new variable PERF=1, otherwise it equals 0. I've been trying to reference the other columns using the monthdiff variable, that's what the problem was. I am new to sas and just based off research, was under the impression I needed to use macros for this case.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
So there is no need for macro code for this.
You can use an array to allow you reference the value of M7 by using an index of 7 into an array of the variables named M1, M2, ....
You have not said how many M variables there are. What is the name of the two date varaibles you need to compare to calculate the target index. So here is a sketch.
data want ;
set have ;
/* How many M variables are there? */
array m m1-m20;
/* Is this the right direction? Or is it lastdate,date1 ? */
monthdiff = intck('month',date1,lastdate) ;
perf = 0;
/* MONTHDIF cannot be one or there is no way to index to monthdiff-1 */
/* MONTHDIF cannot be larger than the size of the array */
if 1 < monthdiff <= dim(m) then if m[monthdiff]='A' and m[monthdiff-1]='Z' then perf=1;
run;
You will need to make it match your dataset. Or ask more directed questions.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
1) Are the 36 variables m1 - m36 new or already in your input?
2) If you already know that there should be 36 variables named m1-m36
why do you need to create their list (created by the loop in t1-t36 variables) ?
3) It seems that you don't need the macro variables at all.
4) You calculate the month difference which is an integer probably has the
value 0 to 36 if I guess correctly.
5) The logic of which variables to check (m1-m36) and what values should
each of them be compared to, is absolutely not clear.
tx(1) = 'm1' cannot be equal to 'A' !!!