topic Re: Arrays, lookup and Transpose in SAS Programming

Arrays, lookup and Transpose

Rkvi — Tue, 27 Aug 2019 20:33:34 GMT

Input: Have, Lookup Datasets. Transpose Have dataset from wide to long.

 data have;
 infile datalines truncover;
   input subj $ vis $ TEST1 $ TEST1_C $ TEST2 $ TEST2_C $;
   datalines;
001 A Q1 9 P1 3
001 A Q5 4 P2 6
;
run;

data lookup;
 input source $ source_c $;
datalines;
TEST1 TEST1_C
TEST2 TEST2_C
;
run;

With the following solution, declaring 2 separate arrays and got the output in desired format.

%let mvars  = TEST1 TEST2  ;
%let mvarsc = TEST1_C TEST2_C  ;

* declaring the lookup avarsc as separate array;

data want;
  set have;
  length source $11. RES $25 ;
   array avars[*] $ &mvars. ;
   array avarsc[*] &mvarsc.;
      
  do i = 1 to dim(avars);
    source = vname(avars[i]); 
	source_c = vname(avarsc[i]);
	RES = avars[i];
	RESN = input(avarsc[i], ??best.);
    output;
  end;

  drop i &mvars. &mvarsc. ;
run;

How do I improve this by having lookup ?

for RESN variable: tried to overcome this explicit declaration of lookup using a format approach.

***********************************************
using formats:
************************************;

proc format;
 value $srcf
	TEST1 = 'TEST1_C'
	TEST2 = 'TEST2_C'
;
run;

data want1;
set have;
  length source $11. RES $25 ;
   array avars[*] $ &mvars. ;
      
  do i = 1 to dim(avars);
    source = vname(avars[i]); 
	source_c = put(source, $srcf.);
	RES = avars[i];

	* use the value variable in PDV of source_c ??;
	RESN = input(source_c, ?? best.) ;
    output;
  end;

  drop i &mvars. &mvarsc. ;
run;

RESN got missing values, as SAS doesn't see this as a variable already in Dataset. How Do I connect these dots of taking the values from variable in PDV.

RESN = using lookup TEST1 has TEST1_C and then use the value of this variable to get numeric value.

Thanks for looking into this. Is this a multilevel lookup or more like &&& in macros.

Re: Arrays, lookup and Transpose

novinosrin — Tue, 27 Aug 2019 21:36:49 GMT

data have;
 infile datalines truncover;
   input subj $ vis $ TEST1 $ TEST1_C $ TEST2 $ TEST2_C $;
   datalines;
001 A Q1 9 P1 3
001 A Q5 4 P2 6
;
run;

data lookup;
 input source $ source_c $;
datalines;
TEST1 TEST1_C
TEST2 TEST2_C
;
run;
data want;
 set have;
 array t(*) test:;
 array temp(99999) $ _temporary_;
 if _n_=1 then do _n_=1 to dim(t);
  temp(_n_)=vname(t(_n_));
 end;
 do i=1 to nobs;
  set lookup point=i nobs=nobs;
  res=t(whichc(source,of temp(*)));
  resn=t(whichc(source_c,of temp(*)));
  output;
 end;
 drop test:;
run;

Re: Arrays, lookup and Transpose

novinosrin — Tue, 27 Aug 2019 21:53:20 GMT

Hash enumerate all is far better than Point=

data have;
 infile datalines truncover;
   input subj $ vis $ TEST1 $ TEST1_C $ TEST2 $ TEST2_C $;
   datalines;
001 A Q1 9 P1 3
001 A Q5 4 P2 6
;
run;



data lookup;
 input source $ source_c $;
datalines;
TEST1 TEST1_C
TEST2 TEST2_C
;
run;

data want;
 set have;
 array t(*) test:;
 array temp(99999) $ _temporary_;
 if _n_=1 then do;
 do _n_=1 to dim(t);
  temp(_n_)=vname(t(_n_));
 end;
 if 0 then set lookup;
  dcl hash H (dataset:'lookup');
  h.definekey  (all:'y');
  h.definedata (all:'y');
  h.definedone ();
  dcl hiter hi('h');
 end;
 do while(hi.next()=0);
  res=t(whichc(source,of temp(*)));
  resn=t(whichc(source_c,of temp(*)));
  output;
 end;
 drop test:;
run;

Re: Arrays, lookup and Transpose

Tom — Wed, 28 Aug 2019 03:27:39 GMT

I really cannot figure out what you are asking.

You original code is dynamic already. Change the list of variables names in the macro variables and re-run to program to tranpose different variables.

What is the purpose of the change?

What do you think that you are going to "lookup"?

Do you just need help transferring data from a dataset into macro variables?

proc sql noprint;
  select source
       , sourcec
    into :mvars separated by ' '
       , :mvarsc separated by ' '
  from lookup
  ;
quit;

Re: Arrays, lookup and Transpose

Rkvi — Wed, 28 Aug 2019 07:13:09 GMT

@novinosrin Thanks! for the Hash enumerate and having all the variables including _C ones. You had answered another question I was researching on - creating an array from another one using first do loop.

@Tom That's how I am getting 2 lists of macros, however the second list is dependent on first one using lookup which wasn't established in my code.

Here's the sections: can we get mvarsc macro using source rather than reading as a independent column.

array avars[*] $ &mvars. ;
   array avarsc[*] &mvarsc.;
      
  do i = 1 to dim(avars);
  	RES = avars[i];
	RESN = input(avarsc[i], ??best.);
  end;

2) Another question while working on this - we have avarsc[1] = Test1, assigning as RES=avarsc[1] gets the value of variable Test1 to RES.

Now for RESN - using the format approach we can get RESN=put(avarsc[1], $srcf.) we get RESN=TEST1_C. is there a way to assign here value of the variable Test1_C like its in PDV get that value of the variable.

Macros we do that using && and &&& to go one more level to get the value.

Any material to read up on this will be great.

Re: Arrays, lookup and Transpose

Tom — Wed, 28 Aug 2019 13:58:32 GMT

I still don't get it.

If the second name is derived from the first name then change the column definition in the SELECT statement.

select var,put(var,$fmt.) into ....

If you want to find the value of a variable whose name you don't know until run time you will need to use VVALUEX() function.

name='TEST';
value=vvaluex(name);

Note this will get you the formatted (character) value of the variable.

Re: Arrays, lookup and Transpose

Rkvi — Wed, 28 Aug 2019 18:11:50 GMT

Thanks for sharing about VVALUEX.

Let me rephrase the problem: For have dataset with one TEST1 in a wide format:

Transpose the data to have two new variables: RES - containing value of TEST1 variable.

RESN - using TEST1 variable, check in lookup and it has Code2: assign values of this as follows:

For RESN: if the lookup updates to use Code1 for Test1, we get RESN values of Code1 variable 9, 4 as below:

Once we add more columns to HAVE dataset in the wide format along with updated lookup, using the dynamic code we should get a normalized dataset.

Re: Arrays, lookup and Transpose

Tom — Wed, 28 Aug 2019 21:11:26 GMT

Which is what your original program was doing.

The only issue with your ARRAY approach is that all of the variables in target list need to use the same type, but since you a pushing the results into the same variable then it shouldn't matter.

Another way is to just use your "lookup" table to generate some "wallpaper" code. For example a step like this converts each observation into two output observations.

data want;
  set have;
  res=test1;
  resn = code1;
  output;
  res=test2;
  resn = code2;
  output;
  keep subj res resn;
run;

Here is logic to generate that type of code from a file that has the names of the variable pairs and then use %INCLUDE to run that code in a data step.

filename code temp;
data _null_;
 set lookup;
 put 'res='  source ';'
   / 'resn=' source_c ';'
  / 'output;'
  ;
run;
data want;
  set have;
  %include code / source2;
  keep subj res resn;
run;