Hi all,   I'm trying to select variable names and want to sum them to create a new variable. Following code works:   proc sql noprint; select distinct FieldName into : name_list separated by ',' from master_data; quit; %put &name_list; data results_final; set results_final; summary = sum(&name_list); run; Now I want to do the same thing with an additional condition that involves a where clause. This is not working. could you please help?   proc sql noprint; select distinct FieldName where male = 1 into : male_name_list separated by ',' from master_data; quit; %put &male_name_list; data results_final; set results_final; summary_male= sum(&male_name_list); run; ... View more

Hi,   I have a very simple problem, but it's bugging me. I have multiple datasets with same number of columns and column name that I want to merge vertically. The following code works:   data results; set result_period_1 result_period_2 result_period_3; run; However, total no of periods is a lot, and I want to have a simple macro/loop that achieves the same thing. I tried proc append, but didn't work:   %macro append; proc append base=results_period_1 data= %do i = 1 %to 3; results_period_&i %end; run; %mend append; %append; Appreciate your help! ... View more

Hi all,   I'm trying to write a SAS macro for a time series and my time variable is "year_period", i.e. 20171, 20172, 20173, 20174, 20181, .....   I'm having a hard time to increment the variable. I'm trying to accomplish something like:   %macro projections(year_period); %DO i = 1 %TO 8; /********code block*****/ %let if mod(year_period,10) LE 3 then year_period = %eval(year_period +1); %let if mod(year_period,10) > 3 then year_period = %eval(year_period +7); %END; %mend projections; But the %let and %eval statements are not correct.Could you please help? ... View more

Hi all,   I have a nxn matrix (for simplicity assuming n=3) like following:   proc iml; A = {0.1 0.2 0.5, 0.2 0.4 0.6, 0.2 0.3 0.8}; I have another table - a reference table, which specifies which values from this nxn (n=3) matrix needs to be written to a new data table. that table is following:   data output_table; infile datalines dsd; length FieldName $20 FieldSourceTable $20; input Matrix_Id Row_Order Column_Order IsZero FieldName FieldSourceTable; datalines; 3, 1, 1, 1, ., . 3, 1, 2, 0, result1, table2 3, 1, 3, 0, result1, table2 3, 2, 1, 1, ., . 3, 2, 2, 0, sum1, table2 3, 2, 3, 0, sum2, table2 3, 3, 1, 0, prod1, table2 3, 3, 2, 0, prod2, table2 3, 3, 3, 1, ., . ; Assume the matrix A is my Matrix_ID 3. So, I need to create a new table called table 2 with the names specified in 'FieldName' column in the reference table and write specific values from the matrix to the table (e.g. (2,3)th element to the column 'sum2'). The following code works for the purpose:   proc sort data= output_table out= output_table; by Row_Order Column_Order; quit; proc iml; use output_table; /* parameterization table for matrix 3 (output table)*/ read all var {Column_Order Row_Order FieldName}; close; idx = loc(FieldName ^= " "); refNames = FieldName[idx]; A = {0.1 0.2 0.5, 0.2 0.4 0.6, 0.2 0.3 0.8}; /*A is the full matrix to write*/ C = A[idx]; C = t(C); mattrib C colname=refNames; print C;   The ouput matrix C looks like following:   result1 result1 sum1 sum2 prod1 prod2 0.2 0.5 0.4 0.6 0.2 0.3   What I want to do is, whenever I have two or more elements of the matrix to be written to the same row, I want them to be summed up. So, my ideal output in this case would be to sum up 0.2 and 0.5 and have 0.7 as result1 as the following output matrix (let's call it D):   result1 sum1 sum2 prod1 prod2 0.7 0.4 0.6 0.2 0.3   Then I'd write the matrix D to a data table: create data_to_write from D[colname = refNames]; append from D; /** create data set and append in each subsequent steps**/ end;    Not being able to sum the elements with same column name to create the matrix D for all cases of same column name. Could you please help?    ... View more

Hi,   I have a parametrisation table and a data table. For each row of data table, I am creating a transition matrix, multiplying that matrix to a constant matrix element-wise and trying to write specific elements of output matrix to a new table.   The following code works perfectly without the do loop, i.e. it only works for one row of table1 at a time. The writing ("create" and "append" statement within a do loop) does not work when I have the do loop. Ideally I want for one run of the loop a new row will be appended to the table2 that I am creating.   /********create parametrisation table*********/ data param_table; infile datalines dsd; length FieldName $20 FieldSourceTable $20; input Matrix_Id Row_Order Column_Order IsZero IsRowResidual IsColumnResidual FieldName FieldSourceTable; datalines; 1, 1, 1, 0, 1, 0, ., . 1, 1, 2, 0, 0, 0, xyz, table1 1, 1, 3, 0, 0, 0, abc, table1 1, 2, 1, 1, 0, 0, ., . 1, 2, 2, 0, 0, 0, pqr, table1 1, 2, 3, 0, 0, 0, mno, table1 1, 3, 1, 0, 0, 0, ab, table1 1, 3, 2, 0, 0, 0, pq, table1 1, 3, 3, 0, 1, 0, ., . 2, 1, 1, 1, 0, 0, ., . 2, 1, 2, 0, 0, 0, result1, table2 2, 1, 3, 0, 0, 0, result2, table2 2, 2, 1, 1, 0, 0, ., . 2, 2, 2, 0, 0, 0, sum1, table2 2, 2, 3, 0, 0, 0, sum2, table2 2, 3, 1, 0, 0, 0, prod1, table2 2, 3, 2, 0, 0, 0, prod2, table2 2, 3, 3, 1, 0, 0, ., . ; proc sort data= param_table out= param_table; by Matrix_Id Row_Order Column_Order; quit; proc sql; create table param_tr as select * from work.param_table where Matrix_Id = 1; quit; proc sql; create table param_flow as select * from work.param_table where Matrix_Id = 2; quit; /********create data table*********/ data table1; input Year (country method Segment) ( : $12.) ABC XYZ PQR MNO AB PQ; datalines; 2017 France ABC Retail 0.2 0.5 0.4 0.3 0.6 0.1 2017 France XYZ Corporate 0.1 0.5 0.4 0.2 0.6 0.2 ; run; /*********build matrices and write out*********/ proc iml; use param_tr; read all var {Column_Order Row_Order FieldName}; close; idx = loc(FieldName ^= " "); refNames = FieldName[idx]; use param_flow; read all var {Column_Order Row_Order FieldName}; close; idx_2 = loc(FieldName ^= " "); refNames_2 = FieldName[idx_2]; use table1; read all var refNames into Y; close; /******Create 3x3 EAD transition matrix for each row of table 1*****/ /*do i = 1 to nrow(Y);*/ /*I want to include the loop*/ TR_MAT = j(3,3,0); TR_MAT[idx_2] = Y[2,]; /*this should be Y[i,]*/ temp = J(3,1)- TR_MAT[,+]; do j=1 to 3; TR_MAT[j,j] = temp[j,1]; end; EAD_INIT = {10, 20, 33}; EAD_flows=(EAD_INIT#TR_MAT); /**********write elements of EAD flow out*********/ EAD_flow_MAT = EAD_flows[idx_2]; EAD_flow_MAT = t(EAD_flow_MAT); mattrib EAD_flow_MAT colname=refNames_2; create table2 from EAD_flow_MAT[colname = refNames_2]; append from EAD_flow_MAT; /** create data set **/ close table2; /*end;*/ *******I want to have the do loop How can I write the result of each row table1 to different rows of "table2" within a loop? Thanks!   ... View more