Windows 10, Version 9.4
Good day,
The following code re-creates dataset beta whereby each value is multiplied by the corresponding column value in variable "fix" of dataset alpha:
data work.alpha;
infile datalines;
length nameo $5.;
input nameo $ fix;
datalines;
sam 0
david 3
karen 4
mike 8
;
run;
data work.beta;
infile datalines;
input sam david karen mike;
datalines;
5 7 8 4
6 4 8 9
7 3 2 6
;
run;
options symbolgen;
%let NumComp=5;
proc sql noprint;
select distinct fix into :fixit1-:fixit&NumComp
from work.alpha
;
quit;
data work.want;
set beta;
sam=sam*&fixit1;
david=david*&fixit2;
karen=karen*&fixit3;
mike=mike*&fixit4;
run;
proc print data=work.want; title 'want final'; run;
The code works and produces the desired results. However, the actual program is longer and I would like to be able to loop through "fixit1","fixit2",...,"fixit#"
Any suggestions are greatly appreciated.
Yours, Jane
You should be able to do this with arrays. Use PROC SQL to get the list of names and values into space delimited lists.
data alpha;
input nameo :$32. fix @@;
datalines;
sam 0 david 3 karen 4 mike 8
;
data beta;
input sam david karen mike @@;
datalines;
5 7 8 4 6 4 8 9 7 3 2 6
;
proc sql noprint;
select nameo,fix
into :vars separated by ' '
, :values separated by ' '
from alpha
;
%let n=&sqlobs;
quit;
data want ;
set beta ;
array vars &vars ;
array fix (&n) _temporary_ (&values);
do _n_=1 to &n ;
vars(_n_) = vars(_n_)*fix(_n_);
end;
run;
Obs sam david karen mike 1 0 21 32 32 2 0 12 32 72 3 0 9 8 48
When you say the actual program is longer, I assume that means you have many more names. A safer way to go would be to change the names of the macro variables you compute. Instead of creating &FIXIT1, for example, create &SAM. That's easily done in a DATA step:
data _null_;
infile datalines;
length nameo $5.;
input nameo $ fix;
call symputx(nameo, fix);
datalines;
sam 0
david 3
karen 4
mike 8
;
run;
Now you don't need to worry about the order of the names being the same in both data sets. The idea is to replace these statements:
sam=sam*&fixit1;
david=david*&fixit2;
karen=karen*&fixit3;
mike=mike*&fixit4;
Instead, you would use the equivalent of:
sam=sam*&sam;
david=david*&david;
karen=karen*&karen;
mike=mike*&mike;
There are a few ways to generate that equivalent code. But let's take that as a starting point and see if this approach makes sense from your point of view.
PROC IML will do this type of multiplication easily.
Also, if you don't have PROC IML, you could turn the row in to a column via PROC TRANSPOSE, combine that new data set with the data set that has the column (now your data is in two columns in the same data set) and then multiplication is easy.
In either case, as long as the ordering of the values in the row is the same as the ordering of the values in the column, then you don't need to type out the names.
Paige
Thank you for the quick reply.
Unfortunately, "proc IML" is not available.
A transpose would definitely help to merge the datasets.
Thanks again,
Jane
data work.alpha;
infile datalines;
length nameo $5.;
input nameo $ fix;
datalines;
sam 0
david 3
karen 4
mike 8
;
run;
data work.beta;
infile datalines;
input sam david karen mike;
datalines;
5 7 8 4
6 4 8 9
7 3 2 6
;
run;
proc transpose data=alpha out=_a prefix=fixit_;
var fix;
id nameo;
run;
data want_beta;
set beta;
if _n_=1 then set _a;
array t(*)sam--mike;
array j(*) fixit:;
do i=1 to dim(t);
t(i)=t(i)*j(i);
end;
drop fixit:;
run;
Novin
The code works and avoids the need for creating so many macros.
Beautiful example and greatly appreciated.
Thank you,
Jane
You should be able to do this with arrays. Use PROC SQL to get the list of names and values into space delimited lists.
data alpha;
input nameo :$32. fix @@;
datalines;
sam 0 david 3 karen 4 mike 8
;
data beta;
input sam david karen mike @@;
datalines;
5 7 8 4 6 4 8 9 7 3 2 6
;
proc sql noprint;
select nameo,fix
into :vars separated by ' '
, :values separated by ' '
from alpha
;
%let n=&sqlobs;
quit;
data want ;
set beta ;
array vars &vars ;
array fix (&n) _temporary_ (&values);
do _n_=1 to &n ;
vars(_n_) = vars(_n_)*fix(_n_);
end;
run;
Obs sam david karen mike 1 0 21 32 32 2 0 12 32 72 3 0 9 8 48
Tom
Thank you for the solution - it works perfectly
It is also my choice because the datasets are actually very large. Thus, potentially using "proc transpose", as suggested by others, should perhaps be avoided as, at least in my mind and I could be totally wrong, this procedure weighs heavy on a LONG program that needs to run as quickly as possible.
Creating a temporary array and adopting &sqlobs to monitor dimensions is ideal.
Thanks again,
Jane
Hi @jawhitmire Hash might make it a touch faster
data work.alpha;
infile datalines;
length nameo $5.;
input nameo $ fix;
datalines;
sam 0
david 3
karen 4
mike 8
;
run;
data work.beta;
infile datalines;
input sam david karen mike;
datalines;
5 7 8 4
6 4 8 9
7 3 2 6
;
run;
data want_b;
if _n_=1 then do;
dcl hash H (ordered: "A") ;
h.definekey ("_iorc_") ;
h.definedata ("fix") ;
h.definedone () ;
dcl hiter hh('h');
do _iorc_=1 by 1 until(lr);
set alpha end=lr;
rc=h.add();
end;
end;
set beta;
array t(*)sam--mike;
do i=1 to dim(t);
rc=h.find(key:i);
t(i)=t(i)*fix;
end;
drop rc i nameo fix;
run;
Temporary array is a good idea but I think populating N macro vars is not needed when the PDV can hold
data want;
do _n_=1 by 1 until(lr);
set alpha end=lr;
array t(9999)_temporary_;
t(_n_)=fix;
end;
lr=0;
do until(lr);
set beta;
array j(*) sam--mike;
do _n_=1 to dim(j);
j(_n_)=j(_n_)*t(_n_);
end;
output;
end;
keep sam--mike;
run;
I know there are already several good solutions here, but there's a technique that should not be overlooked.
filename recode 'path to some file';
data _null_;
infile datalines;
input name $ fix;
file recode;
put name '=' name '*' fix ';' ;
datalines;
sam 0
david 3
karen 4
mike 8
;
That creates a file holding all the recoding equations. Then:
data want;
infile datalines;
input sam david karen mike;
%include recode;
datalines;
5 7 8 4
6 4 8 9
7 3 2 6
;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.