Dear All, I have dataset db1 including variables: id, start_date, end_date, and V1. data db1;      input id    start_date     end_date     V1; datalines; 101     20130126     20130814     19      101     20130729     20130829     77 102     20130201     20130401     14 103     20130217     20130225     11 104     20130102     20131119     47 ; I also have dataset db2 including variables: id, date, and V2. data db2;      input id     date     V2; datalines; 101     20130719     26      103     20130224     55 105     20131107     10 ; I would like to create a variable FLAG in the dataset db1 such that FLAG = 1 if db1.id = db2.id and db2.date >= db1.start_date and db2.date <= db1.end_date. That is, I would like to obtain the following data want;      input id    start_date     end_date     V1     flag; datalines; 101     20130126     20130814     19     1      101     20130729     20130829     77     . 102     20130201     20130401     14     .     103     20130217     20130225     11     1 104     20130102     20131119     47     . ; My attempt involves PROC SQL and I'm able to isolate the desired records. However, I'm not able to attach a flag in the original dataset db1 proc sql;   create table want   as select db1.*   from db1, db2   where db1.id = db2.id and db2.date >= db1.start_date and db2.date <= db2.end_date; quit; Any help would be highly appreciated. ... View more

Dear All, I have dataset db1 containing a vector of x-points and a vector of y-points for each date. data db1;      input date     x      y ; datalines; 20130101     9      0.055 20130101     77    0.049 20130101     140  0.067 20130102     11    0.034 20130102     47    0.041 20130102     82    0.057; I also have dataset db2 containing the points (variable sel_x) at which I would like to linearly interpolate the variable y for each date. data db2;      input date     sel_x; datalines; 20130101     17 20130101     45 20130101     80 20130102     22 20130102     38 20130102     59; I would like to obtain the interpolated values of the variable y (interp_y😞 data want;      input date     sel_x     interp_y; datalines; 20130101     17     0.05429    20130101     45     0.05182 20130101     80     0.04985 20130102     22     0.03613 20130102     38     0.03925 20130102     59     0.04648; My unsuccessful attempt involves a double do loop, it stores the x and y when db1.date = db2.date and performs the linear interpolation by using code from this blog Linear interpolation in SAS - The DO Loop: DO i = 1 TO nrow(db1);   DO j=1 to nrow(db2); if db1.date = db2.date , then do;          x = db1.x ;          y = db1.y ; interp_y = LinInterp1(x, y, sel_x);      end;    END;    END; Any help would be highly appreciated. ... View more

Dear All, I have the following two datasets: data big;      input Obs ID V1; datalines; 1 1 1 2 2 1 3 2 2 4 3 1 5 3 2 6 3 3 7 4 1 8 4 2 9 4 3 10 5 1; data small;      input Obs ID; datalines; 1 2 2 4 3 5; I would like to obtain the dataset WANT, which contains all variables in the dataset BIG and all observations in dataset BIG with a value of the variable ID that occurs in the dataset SMALL. That is, I would like to obtain the following: data want;      input Obs ID V1; datalines; 1 2 1 2 2 2 3 4 1 4 4 2 5 4 3 6 5 1; I have managed to do it by using PROC SQL. This is my attempt: proc sql; create table want as select big.* from big, small where big.id = small.id; quit; However, I also need to create a macro because I have many datasets BIG. Here, I have problems with syntax of PROC SQL inside a macro. The following code doesn't work. %macro sampling(big); proc sql; create table want as select &big.* from &big., small where &big.dt = small.dt; quit; proc append base=final data=want; proc delete data=want; run; %mend; Any help would be highly appreciated. ... View more

Dear All, I would like to select all rows of a dataset satisfying a certain condition (when the variable V1 = 0) AND also keep the observation/row preceding each of them. That is, given database db1 data db1;      input id V1 V2; datalines; 1     2     2.1 1     0     2.9 1     2     3.1 2     4     1.7 2     0     4.5 2     0     0.5. 2     5     2.8 ; I would like to obtain the following data want;      input id V1 V2; datalines; 1     2     2.1 1     0     2.9 2     4     1.7 2     0     4.5 2     0     0.5. ; Notice that I would like to avoid duplicates. Otherwise, I would have the 5th observations twice in the database want. I'm able to select rows matching my condition by using the following code: data want; if V1=0; run; Any help would be highly appreciated. ... View more

Dear All, I have the following two datasets (both sorted with respect to the variables id and date): data db1;      input id date V1 V2; datalines; 1     20110902     0.5     2.1 2     20110903     0.7     2.8 ; data db2;      input id date V3; datalines; 1     20110901     10 1     20110902     20 1     20110903     30 2     20110901     60 2     20110902     50 2     20110903     40 ; I would like to obtain the following dataset db3: data db3;      input id date V1 V2 V3; datalines; 1     20110902     0.5     2.1     20 2     20110903     0.7     2.8     40 ; That is, I would like to add the variable V3 to the first dataset db1 when the values of the variables ID and DATE in db1 coincide with those in db2. Hence, the third dataset must have the size of the first one. I have done two attempts. The first one involves PROC SQL proc sql;   create table db3   as select db1.*, db2.V3   from db1, db2   where db1.id = db2.id and db1.date = db2.date; quit; The second one involves a simple merge with respect to both key variables data db3;   merge db1 db2;   by id date; run; Any help would be highly appreciated. ... View more

Dear All, I need to call multiple datasets from the same library in SAS and change the format of one variable called DATE1 in both datasets. All the datasets have the same column variables because I have one dataset per year. I have managed to do it by using the following code (case 2 datasets): data dataset1 dataset2;   set exp.dataset1 exp.dataset2 ;   date1_mod = INPUT(PUT(date1,8.),YYMMDD8.);   format     date1_mod YYMMDD8. run; This code works, but I would like to ask you whether there is a more efficient way to do it (I may need to call 15-20 datasets). Once I import the datasets, I need to use PROC SQL and CREATE TABLE in order to perform another operation on both datasets. The code below works in the case of a single dataset, but it fails with multiple datasets. My first attempt tries to extend the case with 1 dataset in the following way: proc sql;   create table mod_dataset1 mod_dataset2 as   select V1, V2, V3, V4, V5, V6, month(date1_mod) as month   from dataset1 dataset2   group by V1, calculated month   having date1_mod=max(date1_mod); quit; My second attempt involves the creation of a function MYFUN. This doesn't work either. * creating function myfun; function myfun(dataset); proc sql;   create table newdataset as   select V1, V2, V3, V4, V5, V6, month(date1_mod) as month   from dataset   group by V1, calculated month   having date1_mod=max(date1_mod); quit; endsub; run; * calling function myfun; newdataset = myfun(dataset); Any help would be highly appreciated. ... View more

Dear All, I would like to compute stock returns from prices. I have the following dataset: data stocks;    input key V1 V2; datalines; 1 0.1   10 1 0.2   15 2 0.3   25 2 0.4   30 2 0.5   33 ; where "key" is the company id and "V2" is the share price. I would like to obtain the variable V3 = V2(i)/V2(i-1) -1, but only for observations with the same value of the variable key. I would like to have a blank cell when either the V2(i-1) observation is missing or key(i) is different from key(i-1). key V1 V2 V3 1 0.1 10  . 1 0.2 15 0.5 = (15/10)-1 2 0.3 25 2 0.4 30 0.2 = (30/25)-1 2 0.5 33 0.1 = (33/30)-1 My attempt involves the procedure expand. First, I was trying to obtain the variables key and V2 shifted backword by 1 (i.e., key_lead and V2_lead). Then, I was using a conditional statement to check whether two consecutive observations have the same value of the variable "key" and computing the variable V3 as V2/V2_lead -1. However, SAS returns me an error. proc expand data=stocks out=stocks_lead method = none;    convert key = key_lead  / transformout=(lead 1);   convert V2 = V2_lead  / transformout=(lead 1);   if key_lead = key   then V3 = V2/V2_lead - 1;   else V3 = .; run; Any help would be highly appreciated. ... View more