I am having trouble with the update statement using a join to get create a calculation.
I need to use an innerjoin to get an aggregate value from one database to create a new calculated field. This needs to be done to the same file to create three new fields (in this example) but the number of variables and names of variables will differ so I am using macro variables. I have the proc sql set up in a loop so rather than overwrite the file with a create table I want to use UPDATE instead. At first I was using the same file in the update statement as I was in the inner join but was getting an error as if I was trying to edit an open file. I thought this had something to do with the loop so I created a subset from the full table as the one I want to update (I have to subset anyway)..
The following is a simple example that is getting the distribution of each variable across the combinations of the other variables. For example I want to answer: what is the distribution of all colors WITHIN LIKE-COMBINATIONS of other variables? Do you see any syntax error that stands out ? I keep getting an error and I tried an alias for the update table and join to the update table but I am not getting anywhere. I know this is complicated so I included the sample files below.
OPTIONS NOQUOTELENMAX MPRINT ;
%LET list = shade color size ;
%LET numVars = 3 ;
%macro test() ;
%do i=1 %to &numVars ; /*loop through the three grouping variables */
%let current=%scan(&list.,&i);
%let remaining=%sysfunc(tranwrd(%sysfunc(compbl(%sysfunc(tranwrd(&list.,%sysfunc(trim(¤t.)),%str())))),%str( ),%str(,)));
PROC SQL ;
ALTER TABLE subset ADD ¤t.New FLOAT ; /*add a new column to place calculated field*/
UPDATE subset
SET ¤t.New = (select a.value/b.sum
from FullTable as a
INNER JOIN
(select type, id, &remaining., sum(value) as sum
from FullTable
where type = 1
group by id, type ) as b
ON a.id = b.id
and a.type=b.type
%DO n = 1 %TO &numVars.-1;
AND a.%SYSFUNC(COMPRESS(%SCAN(%bquote(&remaining.), &n., ','),%str(,))) = b.%SYSFUNC(COMPRESS(%SCAN(%bquote(&remaining.), &n.,','),%str(,)))
%END ;
) ;
QUIT;
%end;
%mend test;
%test();
sample datasets:
data FullTable;
input id $ shade $ color $ size $ value type ;
datalines ;
1 light blue small 5 1
1 medium blue small 3 1
1 dark blue small 2 1
2 light red large 10 1
2 medium red large 1 1
3 light red large 4 0
3 medium green xsmall 2 0
3 dark green xsmall 9 0
4 light blue small 2 0
4 medium blue small 5 0
4 dark blue small 5 0
5 light red large 4 1
5 medium red large 6 1
6 medium red large 2 0
7 light red large 1 1
7 medium green xsmall 8 1
7 dark green xsmall 6 1
8 light red large 6 1
8 light blue large 9 1
9 dark green small 10 2
9 dark green xsmall 11 2
9 dark green large 12 2
;
run;
/*subsetTable is a subset of FullTable - Type = 1 */
data subset;
input id $ shade $ color $ size $ value type ;
datalines ;
1 light blue small 5 1
1 medium blue small 3 1
1 dark blue small 2 1
2 light red large 10 1
2 medium red large 1 1
5 light red large 4 1
5 medium red large 6 1
7 light red large 1 1
7 medium green xsmall 8 1
7 dark green xsmall 6 1
8 light red large 6 1
8 light blue large 9 1
;
run;
data WANT;
input id $ shade $ color $ size $ value type shadeNew colorNew sizeNew ;
datalines ;
1 light blue small 5 1 0.5 1 1
1 medium blue small 3 1 0.3 1 1
1 dark blue small 2 1 0.2 1 1
2 light red large 10 1 0.909090909090909 1 1
2 medium red large 1 1 0.0909090909090909 1 1
5 light red large 4 1 0.4 1 1
5 medium red large 6 1 0.6 1 1
7 light red large 1 1 1 1 1
7 medium green xsmall 8 1 0.571428571428571 1 1
7 dark green xsmall 6 1 0.428571428571429 1 1
8 light red large 6 1 1 0.4 1
8 light blue large 9 1 1 0.6 1
;
run;
