DATA Step, Macro, Functions and more

update PROC SQL

Reply
Frequent Contributor
Frequent Contributor
Posts: 133

update PROC SQL

[ Edited ]

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(&current.)),%str())))),%str( ),%str(,)));

PROC SQL ;
ALTER TABLE subset ADD &current.New FLOAT ; /*add a new column to place calculated field*/
UPDATE subset
SET &current.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;

 

Respected Advisor
Posts: 3,890

Re: update PROC SQL

[ Edited ]

@CP2

What's the reason that you don't want to create a new table or re-create the existing table with additional variables as not doing so really complicates things.

Where are your tables stored? Are these SAS files or is all the data in a database? And if it's in a database: Are you allowed to create tables and/or drop and create columns?

 

I also don't understand your aggregation logic. Can you please explain how you get to your want table based on the have tables?

Frequent Contributor
Frequent Contributor
Posts: 133

Re: update PROC SQL

Thanks Patrick. Actually you're right about creating the table. Originally I thought updating would be better but I just ended up creating a new table .

Ask a Question
Discussion stats
  • 2 replies
  • 107 views
  • 0 likes
  • 2 in conversation