0

i'm new to SAS Visual Analytics and SAS Data Studio and I want to have a total sum of a column in every row.

My table looks like this...

ID/Amount
1/10
2/20
3/30

And my final table should be like this...

ID/Amount/New_Column
1/10/60
2/20/60
3/30/60

Is their any function or must I work with DATA Steps?

Thank you...

Richard
  • 25,390
  • 3
  • 25
  • 38
  • Can you use `proc sql` there? Or, you can use `proc summary` and then `merge` – samkart Dec 30 '19 at 15:06
  • I have imported an excel file. Is it possible to use proc sql? –  Dec 30 '19 at 15:35
  • Yes. Use it on the sas dataset as you would on any other database. FYI, window function is not supported. – samkart Dec 30 '19 at 15:53
  • I have choosen Transformation->Code->CASL and with this code I get an error... /* Create a copy of the input table to the output table. */ /* This statement should be replaced by the actual code you intend to run. */ table.partition table= {caslib=_dp_inputCaslib name=_dp_inputTable} casout= {caslib=_dp_outputCaslib, name=_dp_outputTable, replace=true} ; PROC SQL; SELECT DISTINCT Stadt FROM _dp_outputTable; QUIT; I'm not sure which table I have to write in the sql statement. Acctually my table is Einwohner_Tabelle. But it is not working... –  Dec 31 '19 at 14:16
  • Umm... First create a table with the required sum. `proc sql; create table blah1 as select sum(amount) as new_column from imported_table; quit;` Then join the new_column back to the imported table. `proc sql: create final_blah as select a.*, b.* from imported_table a cross join blah1 b; quit;`. This creates what you're looking for. – samkart Dec 31 '19 at 14:54
  • SAS also supports `proc sql; create table final_blah as select *, sum(amount) as new_column from imported_table; quit;` – samkart Dec 31 '19 at 14:56
  • I understand absolut what you mean. But which table is my imported table? The session table? _dp_inputTable? And what exactly is data /* Create a copy of the input table to the output table. */ /* This statement should be replaced by the actual code you intend to run. */ table.partition table= {caslib=_dp_inputCaslib name=_dp_inputTable} casout= {caslib=_dp_outputCaslib, name=_dp_outputTable, replace=true} ; ... can i deleat this? –  Dec 31 '19 at 15:13
  • I'm sorry, I'm not sure what that piece of code is doing. Is this something that was generated by SAS? Imported table will be the sas dataset that was created from the excel file. You should be able to see the dataset name in the logs from the import job. – samkart Dec 31 '19 at 20:09
  • I'm using SAS Viya Trial online and I have choosen Code, where you can modify columns and define calculations. Their you can select Data Step and CASL as language. When you open the editor you will find these strange input output tables. –  Jan 04 '20 at 16:25

0 Answers0