05-20-2016 04:50 PM - edited 05-20-2016 04:52 PM
I'm trying to pull in fields called Mort1, Mort2, Mort3, etc. depending on what value is in my variables, MortDuration&i.
This returns text of Mort1, Mort2, etc. instead of the values:
%DO k=&i %TO &j;
CAT(Mort,Calculated MortDuration&i) as Mortality&i,
If I replace the variable I want with a constant, it works:
%SYSFUNC(CAT(Mort,1)) as Mortality&i, (only it always returns Mort1)
And this returns an error:
%SYSFUNC(CAT(Mort,Calculated MortDuration&i)) as Mortality&i,
Here is my original non-macro, very ugly code that was working:
ifn(Calculated MortDuration1=0,Mort0,ifn(Calculated MortDuration1=1,Mort1,ifn(Calculated MortDuration1=2,Mort2,ifn(Calculated MortDuration1=3,Mort3,ifn(Calculated MortDuration1=4,Mort4,.... etc. as Mortality1,
05-20-2016 05:22 PM
Since you don't show any values for Mort1 Mort2 etc I'm guessing that either a FORMAT or INFORMAT may be a better solution.
Is the result supposed to be a text value or numeric?
A format like
0 = 'value'
1 = 'other value'
put(mortduration&i,vmort.) as mort&i
or an invalue and input
And it looks like you Could even use a CASE
%do k=&i %to &j ; /* what ever value bound
when mortduration&k = &k then Mort&k
else <value when none are true
end as <targetvariablename>,
your Ifn is incomplete so can't guess more.
If you provideed more information about actual values, variable names and what the heck is being done overall to those variables it would help.
05-21-2016 12:18 PM - edited 05-21-2016 12:22 PM
Sounds like your problem is that you want to select which of the various MORTx real variables to use based on the value of the MORTDURATIONy real variable instead of the value of your MACRO variable. You need to use two %DO loops. One to loop of the set of variables that you are checking the value of (and hence the names of the new variable you are generating) and an inner loop to loop over the set of possible values. Generate a CASE statement since you are already using SQL syntax.
%let nvar=5 ; %let nvalues=4 ; .... %do y=1 %to &nvar ; ,case %do x=1 %to &nvalues ; when (calculated MortDuration&y=&x) then Mort&x %end; end as Mortality&y %end;
Note that I moved the comma to the beginning of the generated string instead of the end. This is both an easier style for humans to read and it is also easier to generate in this type of %DO loop. It is more likely for this list of variables to be at the end of your SELECT than the beginning since there will normally be id variables preceding them. If they are at the beginning then add another macro variable to contain the comma. Set it to empty before the loop so that the first variable is generated without the leading comma. Then set it to a comma at the end of the loop so that all subsequent variables will have the leading comma.
%let sep=; %do i=1 to 5; &sep.VAR&i %let sep=,; %end;