topic Re: Dynamic Field Creation and Naming in SAS Programming

Dynamic Field Creation and Naming

DavidPhillips2 — Mon, 19 Jul 2021 19:58:46 GMT

Is there a way to automatically do the below;

if Year1 = 1 and academic_period = '200810' then Fall _2008 =1;	Year1	1	200810	Fall _2008
if Year2 = 1 and academic_period = '200710' then Fall _2008 =1;	Year2	1	200710	Fall _2008
if Year3 = 1 and academic_period = '200610' then Fall _2008 =1;	Year3	1	200610	Fall _2008
if Year4 = 1 and academic_period = '200510' then Fall _2008 =1;	Year4	1	200510	Fall _2008
if Year5 = 1 and academic_period = '200410' then Fall _2008 =1;	Year5	1	200410	Fall _2008
if Year6 = 1 and academic_period = '200310' then Fall _2008 =1;	Year6	1	200310	Fall _2008
if Year1 = 1 and academic_period = '200910' then Fall_2009 =1;	Year1	1	200910	Fall_2009
if Year2 = 1 and academic_period = '200810' then Fall_2009 =1;	Year2	1	200810	Fall_2009
if Year3 = 1 and academic_period = '200710' then Fall_2009 =1;	Year3	1	200710	Fall_2009
if Year4 = 1 and academic_period = '200610' then Fall_2009 =1;	Year4	1	200610	Fall_2009
if Year5 = 1 and academic_period = '200510' then Fall_2009 =1;	Year5	1	200510	Fall_2009
if Year6 = 1 and academic_period = '200410' then Fall_2009 =1;	Year6	1	200410	Fall_2009
if Year1 = 1 and academic_period = '201010' then Fall_2010 =1;	Year1	1	201010	Fall_2010
if Year2 = 1 and academic_period = '200910' then Fall_2010 =1;	Year2	1	200910	Fall_2010
if Year3 = 1 and academic_period = '200810' then Fall_2010 =1;	Year3	1	200810	Fall_2010
if Year4 = 1 and academic_period = '200710' then Fall_2010 =1;	Year4	1	200710	Fall_2010
if Year5 = 1 and academic_period = '200610' then Fall_2010 =1;	Year5	1	200610	Fall_2010
if Year6 = 1 and academic_period = '200510' then Fall_2010 =1;	Year6	1	200510	Fall_2010

I'm trying to dynamically create fields based on a formula. The probability of human error in the future is too high if I just use one SAS line per statement here.

The part that I want is the dynamic Fall_XXXX = 1 part based on a formula.

I experimented with *catx(' ', 'Fall', sum(input(substr(academic_period, 1, 4), 4.), 0)) = 1; But I'm missing the syntax to make this line work.

Re: Dynamic Field Creation and Naming

ballardw — Mon, 19 Jul 2021 20:15:14 GMT

I am not sure exactly what your STARTING data looks like.

I am very surprised that "academic_period" values from what appear to be 6 calendar year like values all get assigned the same Fall_XXXX value. Are you sure there are no typos in there?

What was this supposed to do?

sum(input(substr(academic_period, 1, 4), 4.), 0))

You are summing some value with zero. So why bother?

It may help to tell why you have "year1" etc to begin with and what the underlying rules are. This is one case where a working example may not actually provide sufficient information for a general algorithm such as what to do "next year" when Year7=1 and some academic period.

I suspect that there may be a possible solution with Intnx, your academic period and something extracted from the Year value but I am not going to expend much effort trying to derive obscure rules from an example.

I believe we have indicated a preference for starting data as data step code to give us something to work with before this.

Re: Dynamic Field Creation and Naming

DavidPhillips2 — Mon, 19 Jul 2021 20:48:26 GMT


proc sql;
	create table temp (time_variable varchar(50),
	acaedemic_period_base varchar(6),
	want varchar (50)
);
quit;
proc sql;insert into temp(time_variable, acaedemic_period_base, want) values ('Year1', '200810', 'Fall _2008 =1');
insert into temp(time_variable, acaedemic_period_base, want) values ('Year2', '200710', 'Fall _2008 =1');
insert into temp(time_variable, acaedemic_period_base, want) values ('Year3', '200610', 'Fall _2008 =1');
insert into temp(time_variable, acaedemic_period_base, want) values ('Year4', '200510', 'Fall _2008 =1');
insert into temp(time_variable, acaedemic_period_base, want) values ('Year5', '200410', 'Fall _2008 =1');
insert into temp(time_variable, acaedemic_period_base, want) values ('Year6', '200310', 'Fall _2008 =1');
insert into temp(time_variable, acaedemic_period_base, want) values ('Year1', '200910', 'Fall_2009 = 1');
insert into temp(time_variable, acaedemic_period_base, want) values ('Year2', '200810', 'Fall_2009 = 1');
insert into temp(time_variable, acaedemic_period_base, want) values ('Year3', '200710', 'Fall_2009 = 1');
insert into temp(time_variable, acaedemic_period_base, want) values ('Year4', '200610', 'Fall_2009 = 1');
insert into temp(time_variable, acaedemic_period_base, want) values ('Year5', '200510', 'Fall_2009 = 1');
insert into temp(time_variable, acaedemic_period_base, want) values ('Year6', '200410', 'Fall_2009 = 1');
insert into temp(time_variable, acaedemic_period_base, want) values ('Year1', '201010', 'Fall_2010 = 1');
insert into temp(time_variable, acaedemic_period_base, want) values ('Year2', '200910', 'Fall_2010 = 1');
insert into temp(time_variable, acaedemic_period_base, want) values ('Year3', '200810', 'Fall_2010 = 1');
insert into temp(time_variable, acaedemic_period_base, want) values ('Year4', '200710', 'Fall_2010 = 1');
insert into temp(time_variable, acaedemic_period_base, want) values ('Year5', '200610', 'Fall_2010 = 1');
insert into temp(time_variable, acaedemic_period_base, want) values ('Year6', '200510', 'Fall_2010 = 1');

quit;

Re: Dynamic Field Creation and Naming

DavidPhillips2 — Mon, 19 Jul 2021 20:53:55 GMT

The formula I pasted was to dynamically create fields like Fall 2010 and set the value equal to 1 based on a formula; I'm trying to figure out how to use any formula to create a dynamically named field. The fields will be used in a wide table.

Re: Dynamic Field Creation and Naming

Tom — Mon, 19 Jul 2021 21:29:59 GMT

You need to describe better what you are trying to do. If you want to dynamically name a variable you have two choices. Use PROC TABULATE with its ID statement to tell it which variable to use as the name. Or use some code generation.

But perhaps you just want to keep the data in a tall skinny structure instead of creating new variables. You could always create a report that converts it to a more rectangular layoff for viewing.

Re: Dynamic Field Creation and Naming

ChrisNZ — Mon, 19 Jul 2021 22:24:20 GMT

> You are summing some value with zero. So why bother?

This prevents returning missing values. Whether that's a good thing here remains to be seen.

Re: Dynamic Field Creation and Naming

ChrisNZ — Mon, 19 Jul 2021 22:31:55 GMT

> I experimented with *catx(' ', 'Fall', sum(input(substr(academic_period, 1, 4), 4.), 0)) = 1; But I'm missing the syntax to make this line work.

This line will not work. Variable names are all defined when the data step is compiled. You can't have functions to the left of the = sign (exceptions apply).

Your choices are:

- A macro that writes the code creating the variables you want. This code is then compiled as part of the data step.

- Creating variables that contain the values you need and then transposing the table to convert these values into variable names.

Re: Dynamic Field Creation and Naming

DavidPhillips2 — Tue, 20 Jul 2021 01:54:56 GMT

Do you mean I need to create intermediary variables and then save them to the final variable. I'm trying to save the result of the formula string to a data step variable. The logic will create 200 variables for a wide table.

Re: Dynamic Field Creation and Naming

DavidPhillips2 — Tue, 20 Jul 2021 01:57:01 GMT

I'm trying to dynamically create a wide table off of a formula that defines many variables.

Re: Dynamic Field Creation and Naming

ChrisNZ — Tue, 20 Jul 2021 01:57:45 GMT

I am saying all the variable names must be known when the data step compiles, so before it runs. You can't add variables after compilation.

Re: Dynamic Field Creation and Naming

DavidPhillips2 — Tue, 20 Jul 2021 02:07:34 GMT

Can I define them with a length statement and then populate them dynamically like with the catx statement?

Re: Dynamic Field Creation and Naming

ChrisNZ — Tue, 20 Jul 2021 02:13:24 GMT

If you want to go this route (rather than transposing) then you can define the variables in an array statement, and then refer to either the array index (position) or the variable name associated with that index. Like this:

data t;
  array A[3] VAR1 VAR2 VAR3;
  A[1]=1;
  VAR2=2;
run;

Re: Dynamic Field Creation and Naming

DavidPhillips2 — Tue, 20 Jul 2021 02:17:36 GMT

I'm trying to identify the name of the variable backward, the array method predefines the name. Since I'm working backward it might make sense to transpose. I've used transpose before, I'll look into options with it tomorrow.

Re: Dynamic Field Creation and Naming

ChrisNZ — Tue, 20 Jul 2021 02:26:08 GMT

If you don't know the variable names in advance, then transposing is probably better. The other option would be to build the names in a macro and then use the macro to define the array. That's more convoluted.

Having said that, if the names are random, how can you build a program that uses them?

Re: Dynamic Field Creation and Naming

Tom — Tue, 20 Jul 2021 04:28:16 GMT

@DavidPhillips2 wrote:

I'm trying to dynamically create a wide table off of a formula that defines many variables.

PLEASE PROVIDE EXAMPLE DATA.

Show what SAS code you want to create from that data.

If you want to generate code from DATA the easiest way is to use the PUT statement in a data step to write the code to a file. You can then use %INCLUDE to execute the generated code.

Since you haven't really provided any workable example data or code let's just make a test case.

Say you have a dataset name META with two variables: VAR and CODE. The first has the name to use for a new variable and the second has the expression to use to assign a value to that new variable. Then you can generate a series of assignment statement to create the new variables.

filename code temp;
data _null_;
  set meta;
   file print;
   put var '=(' code ');' ;
run;

Now if have another dataset named HAVE that contains the actual data (the variables that are referenced by the CODE values) then you can use the generated code to create new variables by including it into a data step.

data want;
  set have;
%include code / source2;
run;

Re: Dynamic Field Creation and Naming

DavidPhillips2 — Tue, 20 Jul 2021 13:08:15 GMT

The names are not random. They are derived based on a formula. The list will be Fall 2008 - Fall 2020 after being derived.

Re: Dynamic Field Creation and Naming

Tom — Tue, 20 Jul 2021 13:18:12 GMT

@DavidPhillips2 wrote:

The names are not random. They are derived based on a formula. The list will be Fall 2008 - Fall 2020 after being derived.

In that case do NOT use dynamic names. Fix the names and use an array. Then you can use a calcalated YEAR number to index into the array and decide which variable to use to store the value (or which value to read).

So something like:

data want;
  set have;
  array fall [2008:2020] fall2008-fall2020;
  year = ** formula1 **;
  fall[year] = ** formula 2 ** ;
run;

Re: Dynamic Field Creation and Naming

DavidPhillips2 — Tue, 20 Jul 2021 14:10:20 GMT

I follow the logic in general. I'm trying to follow the details. The idea is to populate the fields of the Fall 2008- Fall2020 after identifying which field the measure relates to.


proc sql;
	create table temp (time_variable varchar(50),
	acaedemic_period_base varchar(6),
	want varchar (50),
	measure1 num(1)
);
quit;
proc sql;insert into temp(time_variable, acaedemic_period_base, want, measure1) values ('Year1', '200810', 'Fall _2008 =1', 1);
insert into temp(time_variable, acaedemic_period_base, want, measure1) values ('Year2', '200710', 'Fall _2008 =1', 1);
insert into temp(time_variable, acaedemic_period_base, want, measure1) values ('Year3', '200610', 'Fall _2008 =1', 1);
insert into temp(time_variable, acaedemic_period_base, want, measure1) values ('Year4', '200510', 'Fall _2008 =1', 1);
insert into temp(time_variable, acaedemic_period_base, want, measure1) values ('Year5', '200410', 'Fall _2008 =1', 1);
insert into temp(time_variable, acaedemic_period_base, want, measure1) values ('Year6', '200310', 'Fall _2008 =1', 0);
insert into temp(time_variable, acaedemic_period_base, want, measure1) values ('Year1', '200910', 'Fall_2009 = 1', 1);
insert into temp(time_variable, acaedemic_period_base, want, measure1) values ('Year2', '200810', 'Fall_2009 = 1', 1);
insert into temp(time_variable, acaedemic_period_base, want, measure1) values ('Year3', '200710', 'Fall_2009 = 1', 1);
insert into temp(time_variable, acaedemic_period_base, want, measure1) values ('Year4', '200610', 'Fall_2009 = 1', 0);
insert into temp(time_variable, acaedemic_period_base, want, measure1) values ('Year5', '200510', 'Fall_2009 = 1', 0);
insert into temp(time_variable, acaedemic_period_base, want, measure1) values ('Year6', '200410', 'Fall_2009 = 1', 0);
insert into temp(time_variable, acaedemic_period_base, want, measure1) values ('Year1', '201010', 'Fall_2010 = 1', 1);
insert into temp(time_variable, acaedemic_period_base, want, measure1) values ('Year2', '200910', 'Fall_2010 = 1', 0);
insert into temp(time_variable, acaedemic_period_base, want, measure1) values ('Year3', '200810', 'Fall_2010 = 1', 0);
insert into temp(time_variable, acaedemic_period_base, want, measure1) values ('Year4', '200710', 'Fall_2010 = 1', 0);
insert into temp(time_variable, acaedemic_period_base, want, measure1) values ('Year5', '200610', 'Fall_2010 = 1', 0);
insert into temp(time_variable, acaedemic_period_base, want, measure1) values ('Year6', '200510', 'Fall_2010 = 1', 0);

quit;

data want;
  set temp;
  array fall [2008:2020] Fall_2008-Fall_2020;
  /*find the year referenced*/
  year = catx(' ', 'Fall', sum(input(substr(acaedemic_period_base, 1, 4), 4.), 0));
  /*record the 1 or 0 of the referenced year to the Fall_2008-Fall_2020 array slot*/
run;

Re: Dynamic Field Creation and Naming

Tom — Tue, 20 Jul 2021 14:38:41 GMT

Thanks for posting data. Note here is a much easier way to create such a dataset that is also easier to review as text.

data temp;
  infile cards dsd truncover ;
  input time_variable :$50. acaedemic_period_base :$6. want :$50. measure1 ;
cards;
Year1,200810,Fall _2008 =1,1
Year2,200710,Fall _2008 =1,1
Year3,200610,Fall _2008 =1,1
Year4,200510,Fall _2008 =1,1
Year5,200410,Fall _2008 =1,1
Year6,200310,Fall _2008 =1,0
Year1,200910,Fall_2009 = 1,1
Year2,200810,Fall_2009 = 1,1
Year3,200710,Fall_2009 = 1,1
Year4,200610,Fall_2009 = 1,0
Year5,200510,Fall_2009 = 1,0
Year6,200410,Fall_2009 = 1,0
Year1,201010,Fall_2010 = 1,1
Year2,200910,Fall_2010 = 1,0
Year3,200810,Fall_2010 = 1,0
Year4,200710,Fall_2010 = 1,0
Year5,200610,Fall_2010 = 1,0
Year6,200510,Fall_2010 = 1,0
;

So you can use SCAN() to pull out "words" from your WANT string and use INPUT() to convert those strings into numbers.

data want;
  set temp;
  year = input(scan(want,-2,'_='),32.);
  array fall [2008:2010] Fall_2008-Fall_2010;
  if 2008 <= year <= 2010 then fall[year]=input(scan(want,-1,'='),32.);
run;

proc print;
run;

                   acaedemic_
        time_       period_                                           Fall_    Fall_    Fall_
Obs    variable       base           want         measure1    year     2008     2009     2010

  1     Year1        200810      Fall _2008 =1        1       2008      1        .        .
  2     Year2        200710      Fall _2008 =1        1       2008      1        .        .
  3     Year3        200610      Fall _2008 =1        1       2008      1        .        .
  4     Year4        200510      Fall _2008 =1        1       2008      1        .        .
  5     Year5        200410      Fall _2008 =1        1       2008      1        .        .
  6     Year6        200310      Fall _2008 =1        0       2008      1        .        .
  7     Year1        200910      Fall_2009 = 1        1       2009      .        1        .
  8     Year2        200810      Fall_2009 = 1        1       2009      .        1        .
  9     Year3        200710      Fall_2009 = 1        1       2009      .        1        .
 10     Year4        200610      Fall_2009 = 1        0       2009      .        1        .
 11     Year5        200510      Fall_2009 = 1        0       2009      .        1        .
 12     Year6        200410      Fall_2009 = 1        0       2009      .        1        .
 13     Year1        201010      Fall_2010 = 1        1       2010      .        .        1
 14     Year2        200910      Fall_2010 = 1        0       2010      .        .        1
 15     Year3        200810      Fall_2010 = 1        0       2010      .        .        1
 16     Year4        200710      Fall_2010 = 1        0       2010      .        .        1
 17     Year5        200610      Fall_2010 = 1        0       2010      .        .        1
 18     Year6        200510      Fall_2010 = 1        0       2010      .        .        1

But that seems really silly. Don't you just want to pull the year from the academic variable instead?

If so then how do you get from academic year of "200710" or "200610" to FALL_2010?

Perhaps by adding the digit in the TIME variable?

data want;
  set temp;
  base_year=input(acaedemic_period_base,4.);
  offset=input(substr(time_variable,5),32.);
  array fall [2008:2010] Fall_2008-Fall_2010;
  fall[base_year+offset-1]=1;
run;

Re: Dynamic Field Creation and Naming

Tom — Tue, 20 Jul 2021 15:04:53 GMT

To make it truly dynamic use PROC TRANSPOSE.

data step1;
  set temp;
  calendar_year=input(acaedemic_period_base,4.)+input(substr(time_variable,5),32.)-1;
run;

proc transpose data=step1 prefix=FALL_ out=want(drop=_name_);
  by time_variable acaedemic_period_base notsorted;
  id calendar_year;
  var measure1;
run;

                   acaedemic_
        time_       period_      FALL_    FALL_    FALL_
Obs    variable       base        2008     2009     2010

  1     Year1        200810        1        .        .
  2     Year2        200710        1        .        .
  3     Year3        200610        1        .        .
  4     Year4        200510        1        .        .
  5     Year5        200410        1        .        .
  6     Year6        200310        0        .        .
  7     Year1        200910        .        1        .
  8     Year2        200810        .        1        .
  9     Year3        200710        .        1        .
 10     Year4        200610        .        0        .
 11     Year5        200510        .        0        .
 12     Year6        200410        .        0        .
 13     Year1        201010        .        .        1
 14     Year2        200910        .        .        0
 15     Year3        200810        .        .        0
 16     Year4        200710        .        .        0
 17     Year5        200610        .        .        0
 18     Year6        200510        .        .        0