Multiplying Every Observation by Column in Another Dataset

[jawhitmire]

Windows 10, Version 9.4, no access to SAS/STATS thus cannot use "proc iml"

Good day,

The following code successfully multiplies each observation in work.beta2 by the corresponding column variable "fix" in work.alpha2.

data work.alpha2;
                infile datalines;
                input fix @@;
                datalines;
            0 1 23 58 72 89 91 76
               ;
               run;

data work.beta2;
                infile datalines;
                input al s zn zr;
                datalines;
             5 7 8 4
			 6 4 8 9
			 7 3 2 6
			 9 8 7 6
			 5 3 1 4
			 7 6 2 8
			 5 7 2 1
			 4 3 7 6
               ;
run;

data work.H_product;
	merge work.alpha2 work.beta2;
	H_al=al*fix;
	H_s=s*fix;
	H_zn=zn*fix;
	H_zr=zr*fix;
	drop al s zn zr fix;
run;

proc print data=work.alpha2; title 'alpha2'; run;
proc print data=work.beta2; title 'beta2'; run;
proc print data=work.H_product; title 'H_Product- Each value is product '; run;

The output is correct and looks like this - but cleaner:

H_Al  H_S H_Zn H_zr

 0        0       0      0

6       4         8     9

161    69    46   138

522     464    406    348

360    216    72    288

623    534    178    712

455    637    182    91

304   228    532   456

The problem is the real file work.beta2 has over 100 variables with 50,000 observations.  Similarly, there are 50,000 values for "fix".  I

I tried to put the values of fix in a macro variable but received out of range type errors. I recall using index variables with SQL, but do not know if these are permitted within SAS.

Any advice, hints, suggestions, or general feedback is greatly appreciated.

Thank you,

Jane

[jawhitmire]

Windows 10, Version 9.4, no access to SAS/STATS thus cannot use "proc iml"

Good day,

The following code successfully multiplies each observation in work.beta2 by the corresponding column variable "fix" in work.alpha2.

data work.alpha2;
                infile datalines;
                input fix @@;
                datalines;
            0 1 23 58 72 89 91 76
               ;
               run;

data work.beta2;
                infile datalines;
                input al s zn zr;
                datalines;
             5 7 8 4
			 6 4 8 9
			 7 3 2 6
			 9 8 7 6
			 5 3 1 4
			 7 6 2 8
			 5 7 2 1
			 4 3 7 6
               ;
run;

data work.H_product;
	merge work.alpha2 work.beta2;
	H_al=al*fix;
	H_s=s*fix;
	H_zn=zn*fix;
	H_zr=zr*fix;
	drop al s zn zr fix;
run;

proc print data=work.alpha2; title 'alpha2'; run;
proc print data=work.beta2; title 'beta2'; run;
proc print data=work.H_product; title 'H_Product- Each value is product '; run;

The output is correct and looks like this - but cleaner:

H_Al  H_S H_Zn H_zr

 0        0       0      0

6       4         8     9

161    69    46   138

522     464    406    348

360    216    72    288

623    534    178    712

455    637    182    91

304   228    532   456

The problem is the real file work.beta2 has over 100 variables with 50,000 observations.  Similarly, there are 50,000 values for "fix".  I

I tried to put the values of fix in a macro variable but received out of range type errors. I recall using index variables with SQL, but do not know if these are permitted within SAS.

Any advice, hints, suggestions, or general feedback is greatly appreciated.

Thank you,

Jane

[Reeza]

SAS/IML and SAS/STATS are independent products. You can check your license with the following:

proc product_status;run;

[jawhitmire]

Thank you for the reply.

Not sure which SAS package supports the <proc IML> procedure.

This is what I have with the Image version information removed for the benefit of privacy for my employer:

For Base SAS Software ...
   Custom version information: 9.4_M5
For SAS/GRAPH ...
   Custom version information: 9.4_M5
For SAS/FSP ...
   Custom version information: 9.4_M5
For SAS/ASSIST ...
   Custom version information: 9.4
For SAS/CONNECT ...
   Custom version information: 9.4_M5
For SAS/ACCESS Interface to PC Files ...
   Custom version information: 9.4_M5

Thanks again,

Jane

[novinosrin]

1.would require pass on the dicitionary columns, concatenate with h_ as prefix on the needed columns and group that into a macro var that will essentially your array elements. 

2. And then a linear boring loop  1 to dim(array);multiply; end;

But you really need to give us the best representative of your real so,we can avoid going back and forth. My 2 cents

[jawhitmire]

Novinosrin

Thank you for the reply. 

It does makes sense to me - although I will need to review the use of the dictionary.

Background stems from mathematics where one solves problems by first simplifying the question to something a bit more manageable.

Next time, I will work harder to make the material as close to the actual as possible - without offending my employer with security issues.

Thanks again,

Jane

[novinosrin]

Hi @jawhitmire   Thank you for your message. It's all good. I like your piecemeal approach i.e breaking down to simpler problems. Keep the questions coming, i will look forward to solving them. 

PS

STEM Math Background is great to have. Sounds like another champ in the making akin to @FreelanceReinh . Oh well I wish my background was so conventional. 

[Reeza]

You can use arrays. In this case, I would suggest dynamically creating the names using a macro or macro variable. Assuming your inputs are all the same, this should dynamically handle your data. I have not dropped the variables so that you can verify the calculations first. You can uncomment the DROP statement to drop them.

proc sql noprint;
select name into :var_list separated by " " from sashelp.vcolumn where libname='WORK' and memname='BETA2';
select catx("_", "H", name) into :rename_list separated by " " from sashelp.vcolumn where libname='WORK' and memname='BETA2';
quit;
*just in case;
%let N_OBS = &sqlobs;


data work.H_product;
	merge work.alpha2 work.beta2;
	array in(*) &var_list;
	array out(*) &rename_list;
    do i=1 to dim(in);
	out(i) = in(i) * fix;
	end;

*drop &var_list;
	
run;

---

@jawhitmire wrote:

Windows 10, Version 9.4, no access to SAS/STATS thus cannot use "proc iml"

Good day,

The following code successfully multiplies each observation in work.beta2 by the corresponding column variable "fix" in work.alpha2.

data work.alpha2;
                infile datalines;
                input fix @@;
                datalines;
            0 1 23 58 72 89 91 76
               ;
               run;

data work.beta2;
                infile datalines;
                input al s zn zr;
                datalines;
             5 7 8 4
			 6 4 8 9
			 7 3 2 6
			 9 8 7 6
			 5 3 1 4
			 7 6 2 8
			 5 7 2 1
			 4 3 7 6
               ;
run;

data work.H_product;
	merge work.alpha2 work.beta2;
	H_al=al*fix;
	H_s=s*fix;
	H_zn=zn*fix;
	H_zr=zr*fix;
	drop al s zn zr fix;
run;

proc print data=work.alpha2; title 'alpha2'; run;
proc print data=work.beta2; title 'beta2'; run;
proc print data=work.H_product; title 'H_Product- Each value is product '; run;

The output is correct and looks like this - but cleaner:

H_Al  H_S H_Zn H_zr

 0        0       0      0

6       4         8     9

161    69    46   138

522     464    406    348

360    216    72    288

623    534    178    712

455    637    182    91

304   228    532   456

The problem is the real file work.beta2 has over 100 variables with 50,000 observations.  Similarly, there are 50,000 values for "fix".  I

I tried to put the values of fix in a macro variable but received out of range type errors. I recall using index variables with SQL, but do not know if these are permitted within SAS.

Any advice, hints, suggestions, or general feedback is greatly appreciated.

Thank you,

Jane

---

[jawhitmire]

Reeza

This worked really well.  Thank you.

However, when I attempted to apply it to the actual program, there were error messages within the <proc sql> procedure.  The libname was the same, but the memname was changed to match the actual dataset name (without the work-dot part).  Does "name" have to be uniquely the word "name"? Would this possible fail if within a macro?

It looks like a cool way to created macro variables along with a similar, but concatenated macro variable.

Thank you,

Jane 

[Astounding · Accepted solution]

As long as you are dropping the original variables, why bother to create new names?  Just replace the original variables with new values instead:

data H_product;
merge alpha2 beta2;
array nums {*} _numeric_;
do _n_ = 2 to dim(nums);
   nums{_n_} = nums{_n_} * fix;
end;
run;

The DO loop starts with 2, because FIX will be the first numeric variable (as long as ALPHA2 is the first data set mentioned in the MERGE statement).

[Tom]

Since you are not really "merging" on anything why not just use two SET statements?

Place the ARRAY between them.

data want ;
  set have;
  array all _numeric_;
  set fix (keep=fix);
  do over all;
     all=all*fix;
   end;
run;

I know that SAS is trying to make DO OVER go away, but it is so much more logical than having to invent an index variable that has no intrinsic meaning.

Of course the real question is why doe the Original user need to multiply the values?

Perhaps they just need to use the variable FIX with a WEIGHT or FREQ statement in their analysis.