Hello,
I am tying to shift the data in dataset using the macro. I can process the shifting using arrays, but was wondering if this could be done using Macros.
What I have - Dataset with fields - variables1 to variables12 (not stored inan Array. Each value is in separate field)
What I want to do - Create a new dataset using above one and call the macro inside the data step to process the shifting
My problem -
(a) I want the Macro to use the variables from SET DATASET, do the processing and store the values back. I have 140 fields in the dataset and I do not want to send those as arguments to the macro.
(b) If I use CALL EXECUTE with the string as argument, I am getting 'Out of Memory' error as the data is huge.
Is it possible to process the data dynamically using macros for such scenario?
%MACRO SHIFT;
%LET MTHCNT = %SYSFUNC(INTCK(MONTH, Date1, Date2)); /* Date1, Date2 and all other variables from Dataset1 */
%LET MTHCNT1=%EVAL(&MTHCNT. + 1);
%IF &MTHCNT. <= 12 %THEN %DO;
%DO M=&MTHCNT1. %TO 12 %BY 1;
%LET SUB=%EVAL(&M - &MTHCNT.);
Var2_&M = Var2_&SUB;
Var3_&M = Var3_&SUB;
Var4_&M = Var4_&SUB;
%END;
%DO N=1 %TO &MTHCNT. %BY 1;
Var2_&N = 0;
Var3_&N = ' ';
Var4_&N = ' ';
%END;
%END;
%ELSE %DO;
%DO N=1 %TO 12 %BY 1;
Var2_&N = 0;
Var3_&N = ' ';
Var4_&N = ' ';
%END;
%END;
%MEND SHIFT;
DATA
dataset
;
SET
dataset1
;
BY
Var1
;
CALL EXECUTE('%SHIFT');
IF LAST.Va1 THEN CALL EXECUTE('OUTPUT;');
IF LAST.Var1 THEN CALL EXECUTE('RUN;');
RUN;
Can you provide a very brief before/after example (i.e., two data steps) showing how the data looks and how you want it to look?
Sure art297. Please see -
Let's assume Dataset1 contains 1 record with following data -
Var1 = 123456
Date1 = 1JAN2011
Date2 = 1JUN2011
Var1_1 = 1, Var1_2 = 2, Var1_3 = 3, Var1_4 = 4,, Var1_5 = 5, Var1_6 = 6, Var1_7 = 7, Var1_8 = 8, Var1_9 = 9, Var1_10 = 10, Var1_11 = 11, Var1_12 = 12
Now based on the difference between Date1 and Date2 in Months, I want to shift the data in the record.
MthCnt = Diff in months (Date2 and Date1) = 6 - 1 = 5
Diff = 5, so calculate values 6 to 12 as -
(I just realized that my program should decrement the value in DO loop instead of incrementing. i.e %DO M=12 %TO. &MTHCNT1. %BY -1;)
Var1_12 = Var1_7 ( 12- diff = 7)
Var1_11 = Var1_6 ( 11- diff = 6)
Var1_10 = Var1_5 ( 10- diff = 5)
Var1_9 = Var1_4 ( 9- diff = 4)
Var1_8 = Var1_3 ( 8- diff =3)
Var1_7 = Var1_2 ( 7- diff = 2)
Var1_6 = Var1_1 ( 6- diff = 1)
And initialize 1 to 5 values = 0
So my final dataste should look like -
Var1 = 123456
Date1 = 1JAN2011
Date2 = 1JUN2011
Var1_1 = 0, Var1_2 = 0, Var1_3 = 0, Var1_4 = 0,, Var1_5 = 0,
Var1_6 = 1, Var1_7 = 2, Var1_8 = 3, Var1_9 = 4, Var1_10 = 5, Var1_11 = 6, Var1_12 = 7
this is one way you can do it.
data test;
Var1 = 123456;
Date1 = '1JAN2011'd;
Date2 = '1JUN2011'd;
Var1_1 = 1;
Var1_2 = 2;
Var1_3 = 3;
Var1_4 = 4;
Var1_5 = 5;
Var1_6 = 6;
Var1_7 = 7;
Var1_8 = 8;
Var1_9 = 9;
Var1_10 = 10;
Var1_11 = 11;
Var1_12 = 12;
run;
options symbolgen mprint merror mlogic;
%macro shift;
%* Get the list of the variables to be shifted;
proc contents data=test out=name(keep=name where=(name not in ('Var1','Date1','Date2'))); run;
%* Store each variable into a macro variable;
data _null_;
set name nobs=cnt;
call symput(compress('name'||put(_n_,best.)),name);
if _n_ =cnt then call symputx('cnt',_n_);
run;
%* Create a macro variable storing the date difference;
data _null_;
set test nobs=dt_cnt;
x=intck('month',date1,date2);
call symput(compress('dt_dif'||put(_n_,best.)),x);
if _n_ =dt_cnt then call symputx('dt_cnt',_n_);
run;
%* Create the new dataset with the shift;
data new;
set test;
%do j=1 %to &dt_cnt;
%do i=1 %to &&dt_dif&j;
&&name&i = 0;
%end;
%do i=&cnt %to %eval(&&dt_dif&j+1) %by -1;
&&name&i = &&name&i - &&&dt_dif&j;
%end;
output;
%end;
run;
%mend;
%shift
vrmkool - Thank you for your response.
I can not use SYMPUT because date1 and date2 data varies for each record.
Does not look like a macro issue at all. You are just moving data around in the same row of a dataset.
data want ;
set have ;
array v var1_1 - var1_12 ;
diff = intck('month',date1,date2);
do i = 12 by -1 while (i-diff > 0);
v(i) = v(i-diff) ;
end;
do i = 1 to diff ;
v(i) = 0;
end;
run;
What is it that will vary in the macro call? The names of the array variables? The upper limit of 12?
Tom - Thank you for your response.
I know this can be done storing data in an Array. I just wanted to know if this could be done without using Array. Thanks!
It is not "stored" in an array. Arrays in SAS are just a tool to simplify referencing variables.
You should use macro language constructs to build code that cannot be done with the normal language.
ok got it. Will give this a try. Thank you
Hi ... I agree on the "not a macro problem" ...
data have;
Var1 = 123456;
Date1 = '1JAN2011'd; Date2 = '1JUN2011'd;
Var1_1 = 1; Var1_2 = 2; Var1_3 = 3; Var1_4 = 4;
Var1_5 = 5; Var1_6 = 6; Var1_7 = 7; Var1_8 = 8;
Var1_9 = 9; Var1_10 = 10; Var1_11 = 11; Var1_12 = 12;
format date: date9.;
run;
data want;
set have;
array v(12) var1_: ;
diff = intck('month',date1,date2);
do i = 1 to 12;
v(i) = (i gt diff) * (v(i) - diff);
end;
drop i diff;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.