Creating New Variables With Dynamic Suffix

[JJ_83]

Hello, 

I want to create a new batch of all the variables in my dataset for each value of two variables: (1) production_year and (2) quarter. For instance, if production_year = 1984 and quarter = 00, then I want to create and add a new batch of variables to my dataset with the suffix "_1984_00".

Using the code below, I have to first subset my data by production_year and quarter and then merge them back together. I am very new to macros . . . I was able to find a macro online and modify it for static renaming purposes. Since I am working with more than 80 production_year and quarter combinations, I am trying to find a way to do this more dynamically.

Any ideas?

%macro rename(oldvarlist, suffix);
	%let k=1;
	%let old = %scan(&oldvarlist, &k);
		%do %while("&old" NE "");
		rename &old = &old.&suffix;
			%let k = %eval(&k + 1);
		%let old = %scan(&oldvarlist, &k);
	%end;
%mend;

data ohprod.production_1984_00;
	set ohprod.combined_production;
		WHERE	PRODUCTION_YEAR = "1984" and
				QUARTER = "00";
run;
     
data ohprod.production_1984_00_final;
	set ohprod.production_1984_00;

		%rename (ACRES_NO APPROVED_BY COMMENTS_PRODUCTION DAYS DECIMAL_WORKING_INTEREST DT_FIRST_PRODUCTION
				DT_MODIFIED_PRODUCTION DT_RECEIVED ENTERED_BY FLAG_1 MAXIMUM_STORAGE_CAPACITY 
				OIL_STORAGE OWNER_NAME OWNER_NO_PRODUCTION PARCEL_NO PERMIT PRODUCTION_BRINE PRODUCTION_GAS
				PRODUCTION_OIL PRODUCTION_OIL_GAS_TOTAL PRODUCTION_YEAR QUARTER TAXING_DISTRICT UPSIZE_TS_PRODUCTION
				WELL_NAME_PRODUCTION WELL_NO_PRODUCTION YEAR_ORDER, _1984_00);
run;

[Reeza]

IME its easier to transpose your data to a long format and then transpose it so that your variables are named dynamically.

[yabwon]

Hi,

do you want to do something like this:

data have;
input product : $ production_year quarter amount count;
cards;
A 1984 0 100 1
A 1984 1 200 2
A 1984 2 300 3
A 1985 0 400 4
A 1985 1 500 5
B 1984 0 100 1
B 1984 1 200 2
B 1984 2 300 3
B 1985 0 400 4
B 1985 1 500 5
;
run;

proc transpose data = have out = want1(drop=_name_) prefix=amount_ delimiter=_;
  by product;
  var amount;
  id production_year quarter;
run;

proc transpose data = have out = want2(drop=_name_) prefix=count_ delimiter=_;
  by product;
  var count;
  id production_year quarter;
run;

data want;
  merge want1 want2;
  by product;
run;

All the best

Bart

[JJ_83]

Hi Bart,

Yes, that is what I want!

I have about 26 variables, so is there a way to do this so that I don't have to create and then combine 26 datasets? If not, that's totally fine . . . this way is already a huge improvement from the way I've been doing it. 

[yabwon]

Hi, 

I can't recall if it is possible to do it without 26 datasets, but as a replacement maybe this will help:

data have;
input product : $ production_year quarter amount count;
cards;
A 1984 0 100 1
A 1984 1 200 2
A 1984 2 300 3
A 1985 0 400 4
A 1985 1 500 5
B 1984 0 100 1
B 1984 1 200 2
B 1984 2 300 3
B 1985 0 400 4
B 1985 1 500 5
;
run;


%macro tr(have,varlist);
  %local i var;
  %let i=1;
  %let var = %scan(&varlist., &i.);

  %do %while(&var. ne);
    proc transpose data = have out = want_&i.(drop=_name_) prefix=&var._ delimiter=_;
      by product;
      var &var.;
      id production_year quarter;
      format quarter z2.;
    run;

    %let i=%eval(&i.+1);
    %let var = %scan(&varlist., &i.);
  %end;
%mend tr;

%tr(have, amount count);

data want;
  merge want_:;
  by product;
run;

All the best

Bart

[Reeza]

If all your variables are numeric, go to a fully long data set and then transpose it back. Then when you transpose you can use multiple variables, including the variable name as part of the ID statement.

Variable Value Year Quarter
var1   24    2008 1
var2   25    2008 1
...

var1  38  2020 1
...
var15 34 2020 1
;

[yabwon · Accepted solution]

@Reeza - thanks for reminding me the solution 🙂

@JJ_83 the code:

data have;
input product : $ production_year quarter amount count;
cards;
A 1984 0 100 1
A 1984 1 200 2
A 1984 2 300 3
A 1985 0 400 4
A 1985 1 500 5
B 1984 0 100 1
B 1984 1 200 2
B 1984 2 300 3
B 1985 0 400 4
B 1985 1 500 5
;
run;


data have2;
  set have;
  array X amount count;

  do over X;
    var_val = X;
    var_name=vname(X);
    output;
  end;
  drop amount count;
run;

proc transpose data = have2 out = want(drop=_name_) delimiter=_;
  by product;
  var var_val;
  id var_name production_year quarter;
  format quarter z2.;
run;

Bart

[JJ_83]

Thank you, this worked great!

[mkeintz]

This is an excellent justification for sas to enhance proc transpose so that it can create two (or more) output datasets (want1 and want2 in this example) in one pass, instead of the current requirement to use two passes.  Needlessly expensive for large datasets.

I don't think the transpose macro offered by Art Tabachnek (@art297) et al. does this, but you might want to look at

A better way to FLIP (i.e, transpose/make wide) a dataset 

[yabwon]

Mark,

100% agree that SAS should enhance proc transpose. Also thanks for the link to the article.

Bart

[art297]

@mkeintz : The %transpose macro would require two passes of the data as it was only designed for a single variable id. However, the first data pass would be quite simple and, other than simplicity, the macro would have the additional benefit of retaining each variables metadata. e.g.:

filename tr url 'https://raw.githubusercontent.com/art297/transpose/master/transpose.sas';
%include tr ;

data need;
  set have;
  newid=catx('_',production_year,put(quarter,z2.));
run;

%transpose(data=need, out=want2, id=newid,by=product,
 Guessingrows=1000, var=amount count, var_first=yes, delimiter=_)

Art, CEO, AnalystFinder.com