topic Re: How to insert into existing dataset with one of the columns being a sum of other columns? in New SAS User

How to insert into existing dataset with one of the columns being a sum of other columns?

heyyou1 — Fri, 19 Nov 2021 20:32:15 GMT

I would like to do the following:

Sum up the column "Money" in table 2
insert a new row into table1
put NULL values in the other_cols

For example, I have these two tables

TABLE 1
TYPE | MONEY | other_col 1 | other_col 2 | 
A | 1000 | stuff | more
B | 500 | stuff | ok

TABLE 2
TYPE | MONEY | 
C | 750| 
D | 100 |

And Im trying to do a SQL statement that turns table 1 into:

TABLE 1 (Updated)
TYPE | MONEY | other_col 1 | other_col 2 | 
A | 1000 | stuff | more
B | 500 | stuff | ok
CD | 850 | NULL | NULL

Re: How to insert into existing dataset with one of the columns being a sum of other columns?

heyyou1 — Fri, 19 Nov 2021 20:47:23 GMT

I'm trying to do something like this, but I'm not sure if you can nest statements

PROC SQL;
	INSERT INTO table1 (type, MONEY)
	VALUES("CD",  SELECT sum(money) from table2)
	;
QUIT;

But I get this error

 PROC SQL;
 183        INSERT INTO race_sjc_mph_data (race, dose_1_num)
 184        VALUES("Other Race/ Unknown",  SELECT sum(dose_1_num) from race_unknown_other)
                                            ______
                                            22
                                            76
 ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant, 
               a missing value, ), +, ',', -, MISSING, NULL, USER.  
 
 ERROR 76-322: Syntax error, statement will be ignored.

* Notice the variables and tables are name differently. I wanted to be clear with my question *

Re: How to insert into existing dataset with one of the columns being a sum of other columns?

sbxkoenk — Fri, 19 Nov 2021 21:51:14 GMT

Try this :

data work.TABLE_1;
LENGTH TYPE $ 10 MONEY 8 
       other_col_1 $ 8 other_col_2 $ 8;
infile datalines delimiter='|';
input TYPE $ MONEY other_col_1 $ other_col_2 $;
datalines;
A | 1000 | stuff | more
B | 500  | stuff | ok
;
run;

data work.TABLE_2;
LENGTH TYPE $ 10 MONEY 8;
infile datalines delimiter='|';
input TYPE $ MONEY;
datalines;
C | 750 
D | 100 
;
run;

data work.table_3(drop=TYPE MONEY);
LENGTH TYPE $ 10 ret_type $ 10 MONEY 8 ret_money 8 
       other_col_1 $ 8 other_col_2 $ 8;
 set work.TABLE_2 end=last;
 retain ret_type ret_money;
 ret_type  = strip(ret_type) !! strip(type);
 ret_money = SUM(ret_money , money);
 other_col_1 = 'NULL';
 other_col_2 = 'NULL';
 if last then output;
run;

PROC APPEND base=work.TABLE_1 data=work.TABLE_3(rename=(ret_type=type ret_money=money));
run;
/* end of program */

Koen

Re: How to insert into existing dataset with one of the columns being a sum of other columns?

Tom — Sat, 20 Nov 2021 03:59:37 GMT

Don't do that.

First of all it will mess up you input data to insert ANY new observations. So make a NEW dataset, don't insert data into the existing dataset.

Second if you have mixed types of records then the resulting dataset will be impossible to use for anything other than printing. In which case just print the original data and have the reporting procedure include the sum.

proc print data=table1;
  var type money;
  sum money;
run;

Or for your second example use a FORMAT.

proc freq data=have;
  tables race ;
  format race racef. ;
run;

Re: How to insert into existing dataset with one of the columns being a sum of other columns?

Ksharp — Sat, 20 Nov 2021 13:24:48 GMT

Maybe SQL only recognize the number value ,not sub-query of SQL.

PROC SQL;
SELECT sum(money) into :sum from table2 ;

INSERT INTO table1 (type, MONEY)
VALUES("CD", &sum.)
;
QUIT;