I want to group by one column, by "id" in this example, to compute sum for some columns and report the last entry for some other columns (those columns would have to be specified).   Say I have this data: data have; input id time sales bonus commission toalstock totalcash totalvalue; datalines; 1 1 10 1 1 100 50 200 1 2 10 1 1 100 50 200 1 3 15 2 1 80 50 200 1 4 10 1 1 90 50 200 1 5 20 2 2 100 50 200 1 6 23 2 2 133 52 222 ;   My desired output would be: data want; input id time sales bonus commission toalstock totalcash totalvalue; 1 6 88 9 8 133 52 222 ;   My main concern is computational efficiency as I'll be running this on tables with many more columns (about 200) and data entry (about 30000 rows) with different ids.   Proc SQL or native SAS are both welcome. Rows might be shuffled if necessary.     Thanks, Krish ... View more

Hi all,   The idea is I want to create a macro which, based on a selected row in the data set, will pick up the value of a specified column and assign this value to &LTierperc1.   e.g, in the code below, the macro ChargeTier is suppose to assign the value from the third row of Column6 (the column is set as a variable), say that value is 0.78, to LTierperc1. After running the macro, I ran %put &LTierperc1, I got the error message " WARNING: Apparent symbolic reference LTIERPERC1 not resolved". LTierperc1 doesn't exist :(.   Can someone help please, I can't understand why the macro doesn't work. I should mention the code works outside of a macro. Strange thing is if I first run the code outside of a macro and then run the macro, the macro works. But then when I close SAS and after re-opening SAS and running the macro, the macro doesn't work, i.e. LTierperc1, is not created.   Here is the code:   %ChargeTier(column6);   %macro ChargeTier(Chargecol);             data _null_;            set tiering.Chargecode (where=(rownum=3));            call symputx('LTierperc1',&Chargecol.);            run; %mend ChargeTier;       I would also like to expand the code to do the following %CalcCost(Column6, 100);   %macro CalcCost(Chargecol, Amount);            %ChargeTier(&Chargecol);     /*use to obtain the percentage to apply*/            %Charges(&Amount);             /*macro charges invokes a code which will tier the £100 before a cost is calculated*/             run; %mend CalcCost;   Because Column6 is first assigned to &Chagecode (in CalcCost) then as &Chargecol (in ChargeTier), is it ok to to do this? Is there a better way to do this?     Thanks for your help.   Krish ... View more

Hi all,   I have different data tables named NB_PRODUCT_GROUP1_1, NB_PRODUCT_GROUP1_2,....., NB_PRODUCT_GROUP1_24   I need to do the following for each of the above tables located in "C:...\Output_Calculation_Sets\Source" 1. select about 96 columns (TIme, Period length,...,surplus) 2. transpose the data 3. export the data into csv to  the folder "C:...\Output_Calculation_Sets\results" repeat the above for all 24 tables to have 24 csv named nb_grp1_1.csv, nb_grp1_2.csv, nb_grp1_3.csv,...,nb_grp1_24.csv   I'm using the following method, I've added a variable &yer to the source table so that I get a table name with a variable attached to it NB_PRODUCT_GROUP1_&yer. I then run the code below by changing %let yer= 1 to 24 (very manual and time consuming) Can someone help me to create a code which can loop the above.   As bonus, instead of having 24 csv it would help to have all of them in one csv file with tabs nb_grp1_1, nb_grp1_2, nb_grp1_3,...,nb_grp1_24   Thanks in advance. Krish     LIBNAME Source BASE "C:...\Output_Calculation_Sets\Source"; %let yer= 1;   proc sql; create table work.nb_grp1_&yer as select Time, Period_Length, DISC_RATE, ......., MORT_ASS, Surplus FROM Source.NB_PRODUCT_GROUP1_&yer; QUIT; proc transpose DATA = work.nb_grp1_&yer name=Time let OUT = WORK.InputSetFormatted; id Time; proc export DATA = work.nb_grp1_&yer dbms=csv outfile="C:...\Output_Calculation_Sets\results\nb_grp1_&yer..csv" replace; ... View more