Solved: Re: Dynamic Field Creation and Naming - Page 2

DavidPhillips2 · Posted 07-20-2021 09:08 AM

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

Tom · Posted 07-20-2021 09:18 AM

@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;

DavidPhillips2 · Posted 07-20-2021 10:10 AM

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;

Tom · Posted 07-20-2021 10:27 AM

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

Spoiler

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
;

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;

Tom · Posted 07-20-2021 11:04 AM

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

DavidPhillips2 · Posted 07-20-2021 11:08 AM

Tom,

Thanks, for posting this. I was having a hard time solving this one.

DavidPhillips2 · Posted 07-20-2021 01:25 PM

I posted a simplification of what I am looking for. I have a semi-wide table that I am trying to convert to a wide table. Academic_period_base is the starting year. Year1 is one year from the base year. Year2 is two years from the base year. If the student enrolled then 1st year then year1 is 1. If the student enrolled the second year year2 is 1. The formula below adds the 1 or 2 from the year fields to academic_period_base and creates calendar_year1 and calendar_year2. The goal is to create an output that contains variables 2005-2008 that correlate to the values year 1 and year2 in a wide format. The actual wide table will have about 250 wide fields.


proc sql;
create table temp2
(
acaedemic_period_base varchar(6),
year1 num(1),
year2 num(1)
);
quit;

proc sql;
insert into temp2(acaedemic_period_base, Year1, Year2) values ('200810', 1, 1);
insert into temp2(acaedemic_period_base, Year1, Year2) values ('200710', 1, 1);
insert into temp2(acaedemic_period_base, Year1, Year2) values ('200810', 0, 0);
insert into temp2(acaedemic_period_base, Year1, Year2) values ('200710', 1, 0);
insert into temp2(acaedemic_period_base, Year1, Year2) values ('200810', 1, 1);
insert into temp2(acaedemic_period_base, Year1, Year2) values ('200710', 1, 1);
quit;




data step1;
set temp2;
calendar_year1=input(acaedemic_period_base,4.)+input(substr(year1,5),32.)-1;
calendar_year2=input(acaedemic_period_base,4.)+input(substr(year2,5),32.)-2;
run;

DavidPhillips2 · Posted 07-20-2021 02:31 PM

This will work for the full solution.


proc sql;
create table temp2
(
acaedemic_period_base varchar(6),
year1 num(1),
year2 num(1)
);
quit;

proc sql;
insert into temp2(acaedemic_period_base, Year1, Year2) values ('200810', 1, 1);
insert into temp2(acaedemic_period_base, Year1, Year2) values ('200710', 1, 1);
insert into temp2(acaedemic_period_base, Year1, Year2) values ('200810', 0, 0);
insert into temp2(acaedemic_period_base, Year1, Year2) values ('200710', 1, 0);
insert into temp2(acaedemic_period_base, Year1, Year2) values ('200810', 1, 1);
insert into temp2(acaedemic_period_base, Year1, Year2) values ('200710', 1, 1);
quit;

data want;
set temp2;
base_year=input(acaedemic_period_base,4.)-1;
array fall [2007:2010] Fall_2007-Fall_2010;
fall[base_year+1]=year1;
fall[base_year+2]=year2;
if Fall_2007 = . then Fall_2008 =0;
if Fall_2008 = . then Fall_2008 =0;
if Fall_2009 = . then Fall_2009 =0;
if Fall_2010 = . then Fall_2010 =0;
run;

Classroom Training Available!