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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.