BookmarkSubscribeRSS Feed
CP2
Pyrite | Level 9 CP2
Pyrite | Level 9

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;

 

2 REPLIES 2
Patrick
Opal | Level 21

@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?

CP2
Pyrite | Level 9 CP2
Pyrite | Level 9

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 .

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 664 views
  • 0 likes
  • 2 in conversation