CODEWITHSUNDEEP
sas
Ajay
Ajay

Reputation: 1

SAS call symput in data step

I am facing this issue with sas data step. My requirement is to get a list of variables such as

total_jun2018 = sum(jun2018, dep_jun2018);
total_jul2018 = sum(jul2018, dep_jul2018);

Data final4;
set final3;
by hh_no;

do i=0 to &tot_bal_mnth.;
  bal_mnth =  put(intnx('month',"&min_Completed_dt."d, i-1), monyy7.);
  call symputx('bal_mnth', bal_mnth);
  &bal_mnth._total=sum(&bal_mnth., Dep_&bal_mnth.);
  output;
end;

But I am facing error that macro variable bal_mnth not resolved. Also once it did ran successfully but I want that output must be printed sequentially but it only prints output for last loop when i=6 then it prints only Total_DEC2018=sum(DEC2018, DEP_DEC2018);

Any help will be appreciated!

Thanks, Ajay

Upvotes: 0

Views: 917

Answers (3)

Tom
Tom

Reputation: 51566

It looks like you want to generate a series of assignment statements like:

total_jun2018 = sum(jun2018, dep_jun2018); 
total_jul2018 = sum(jul2018, dep_jul2018);
...
total_jan2019 = sum(jan2019, dep_jan2019);

What is known as wallpaper code.

If your variables names were easier, such as dep1 to dep18 then it would be easy to use arrays to process the data. With your current naming convention the problem with generating the array statements is not much different than the problem of generating a series of assignment statements.

You can create a macro so that you could use a %DO loop to generate your wallpaper code. 

%local i bal_mnth;
%do i=0 %to &tot_bal_mnth.;
  %let bal_mnth =  %sysfunc(intnx(month,"&min_Completed_dt."d, &i-1), monyy7.);
  total_&bal_mnth = sum(&bal_mnth , Dep_&bal_mnth );
%end;

Or you could just generate the code to a file with a data step. 

%let tot_bal_mnth = 7;
%let min_Completed_dt=01JUN2018;
filename code temp;
data _null_;
  file code;
  length bal_mnth $7 ;
  do i=0 to &tot_bal_mnth.;
    bal_mnth =  put(intnx('month',"&min_Completed_dt."d, i-1), monyy7.);
    put 'total_'  bal_mnth $7. ' = sum(' bal_mnth $7. ', Dep_' bal_mnth $7. ');';
  end;
run;

So the generated file of code looks like this:

total_MAY2018 = sum(MAY2018, Dep_MAY2018);
total_JUN2018 = sum(JUN2018, Dep_JUN2018);
total_JUL2018 = sum(JUL2018, Dep_JUL2018);
total_AUG2018 = sum(AUG2018, Dep_AUG2018);
total_SEP2018 = sum(SEP2018, Dep_SEP2018);
total_OCT2018 = sum(OCT2018, Dep_OCT2018);
total_NOV2018 = sum(NOV2018, Dep_NOV2018);
total_DEC2018 = sum(DEC2018, Dep_DEC2018);

You can then use %include to run it in your data step.

data final4;
  set final3;
  by hh_no;
  %include code / source2 ;
run;

Upvotes: 1

user667489
user667489

Reputation: 9569

I would like to offer another point of view: the difficulty you are having here results from the use of a wide data shape, with lots of columns.

Rather than working with your data in this shape, you could first transpose from wide to long, so that instead of having lots of total_xxx columns you just have 3: total, total_dep and date, with one row per month. Once it's in this format, it will be much easier to work with, potentially allowing you to avoid resorting to macros and wallpaper code.

Suggested reading:

Transpose wide to long with dynamic variables

Upvotes: 0

Chris Long
Chris Long

Reputation: 1319

This is a common issue when learning SAS Macro. The problem is that the macro processor needs to resolve &bal_mnth to a value when the data step is first submitted for execution, but the CALL SYMPUT doesn't execute until the data step is actually executed, so at the time you submit the code, there is no value available for &bal_mnth.

In this case you don't need bal_mnth to be created as a variable in the data set, so you could replace the line that starts bal_mnth = put(intck(...)) with a %let bal_mnth = ... statement. The %let executes while the data step is being submitted, so that way its value will be available when you need it.

My proposed %let statement will need to wrap the functions in at least one SYSFUNC call, which is left as an exercise for the reader :-)

Upvotes: 1

Related Questions