About SeanZ

SeanZ · ‎05-05-2017

I have a problem like this. I have two tables. One table only contains one variable, patient ID. The second one has two variables, patient ID and another variable, say, patient age. the second dataset is very large. Now I would like to subsample the second dataset to include ONLY patient IDs that are in the first table. I tried to write someting like below, but it didn't work. BY NOT USING merge, is it possible to do this in the similiar way I wrote? Thank you. proc sql; create table want as select * from table b where b.patientID in a.patientID; quit;

SeanZ · ‎02-18-2017

Hi mkeintz. The problem is shown as below. I have a dataset of eight variables, firm ID, year, treat, a, b, c, d, e. For each firm in each year, there are five firm characteristics a - e. Treat is dummy variable taking 1 if a firm is a treated firm and 0 if a firm is a controlled firm. For each year there is only ONE treated firm, and multiple controlled firms. Now for each year each treated firm, I'd like to find a control firm that is the most similar to the treated firm based on a-e. That's why I want to calculate Euclidean distance between treated firm and other control firms. The challenge here is that not all treated/controlled firms have full values for a -e. My idea is to use all available information for each treated firms to calculate distance. For example, if treated firm 001 has all values on a, b, c, d, e, then I would like to calculate distance between 001 and all controlled firms that have all a-e information in a particular year. if treated firm 002 has only values on a, b, c, then I would like to calculate distance between treated 002 and controlled firms that have available information on a, b, c. Do you have any thoughts on this? Thanks.

SeanZ · ‎02-18-2017

The problem is shown as below. I have a dataset of eight variables, firm ID, year, treat, a, b, c, d, e. For each firm in each year, there are five firm characteristics a - e. Treat is dummy variable taking 1 if a firm is a treated firm and 0 if a firm is a controlled firm. For each year there is only ONE treated firm, and multiple controlled firms. Now for each year each treated firm, I'd like to find a control firm that is the most similar to the treated firm based on a-e. That's why I want to calculate Euclidean distance between treated firm and other control firms. The challenge here is that not all treated/controlled firms have full values for a -e. My idea is to use all available information for each treated firms to calculate distance. For example, if treated firm 001 has all values on a, b, c, d, e, then I would like to calculate distance between 001 and all controlled firms that have all a-e information in a particular year. if treated firm 002 has only values on a, b, c, then I would like to calculate distance between treated 002 and controlled firms that have available information on a, b, c. Do you have any thoughts on this? Thanks.

SeanZ · ‎02-18-2017

Thanks. In this case, I would like the code to automatically use all nonmissing values in each observation to calculate distance. 2-dimensional distance is just an example if only nonmissing data exist in this two dimensions. If for example, observation 1 and observation 5 have nonmissing data in all five dimension, I would like to get distance in five dimension.

SeanZ · ‎02-18-2017

Thanks PGStats, my problem is that replacing missing values with zeros is not appropriate in the context. It's hard to predict what missings should be. Therefore, I will like to ignore missing data and use all available data in observations to calculate distance.

SeanZ · ‎02-17-2017

Hi mkeintz, Your first thought is quite right "At first I though you meant euclidean distiance in the 5-dimensional space with each point at it (a,b,c,d,e) dimension. Are you saying that you want a 2-dimensional subspace of some distance just for a subset of points?" In 100 observations, I only care the distance between 1st obs and all others, and try to find the nearest neighbor for the 1st obs. In that sense, I want the 2-dimensional euclidean distance for the example above. Do you have any thought on how to do this? Thanks.

SeanZ · ‎02-17-2017

Hi, I have a general question regarding proc distance. I have a dataset of five variables, a, b, c, d, e, and there are many observations, say, 100. I would like to get a distance matrix between each of 100. All five variables are used in calculation. But there are some missing values in a - e. Now I still want to get distance between each pair and ignore ONLY missing value(s) in that observationn, but not the whole observation. For example, obs 1 has only a, b, c and d, and obs 2 only has a and c. Then I would like to calculate the distance based on a and c. How to adjust that in proc distance? I also need to standardize these variables. Thanks.

SeanZ · ‎01-17-2017

I have a dataset such as below. ID Base x y z 01 0 1.1 2.3 2 02 0 1.9 3.3 2.3 03 1 5.1 2.5 6 04 0 1.1 4.3 4 05 0 3.1 2.3 5 06 0 1.7 1.3 2 Use Base=1 as the main ID, I want to calculate a Euclidean distance based on x, y and z. That is, I want to calculate the distance between each of ID whose base=0 and base=1 (ID 03) based on x,y ,z. I want to add one column dist, which is the distance between base=0 and base=1. The distance is defined as square root of sum of vector(x,y,z). For example, distance between ID01 and ID03 is distance between [1.1, 2.3, 2] and [5.1, 2.5, 6]. I want to get a new dataset as below. ID Base x y z dist 01 0 1.1 2.3 2 distance(ID01,ID03) 02 0 1.9 3.3 2.3 distance(ID02,ID03) 03 1 5.1 2.5 6 distance(ID03,ID03)=0 04 0 1.1 4.3 4 distance(ID04,ID03) 05 0 3.1 2.3 5 distance(ID05,ID03) 06 0 1.7 1.3 2 distance(ID06,ID03) Thanks.

SeanZ · ‎01-17-2017

Thank you for your reply. I paste a new data set to reflect my question. I would like to backfill N years of missing data. In this case, I would like to set N=2. Eventually, I hope to control and set different N. year firm value 2000 A 1 2001 A . 2002 A . 2003 A . 2004 A 9 2000 B 2 2001 B . 2002 B 3 2003 B . 2004 B . 2005 B 2 The data I want year firm value 2000 A 1 2001 A . 2002 A 9 2003 A 9 2004 A 9 2000 B 2 2001 B 3 2002 B 3 2003 B 2 2004 B 2 2005 B 2 Thanks.

SeanZ · ‎01-17-2017

Thank you. But how can this way apply the year screening criterion? I only want to backfill three years of miss data.

SeanZ · ‎01-17-2017

I will define the number of years to fill for missing values.

SeanZ · ‎01-17-2017

Thanks for the solution. I like this solution very much. But how to extend this to the case that I want to backfill 3 years instead if one? Regards, Sean

SeanZ · ‎01-16-2017

There are missing values in my data set, and I want to backfill some missing values. See the example below year firm value 2000 A 1 2001 A . 2002 A . 2003 A 9 2000 B 2 2001 B . 2002 B 3 2003 B 1 I only want to backfill ONE year, after manipulation, I would like to get the results below. year firm value 2000 A 1 2001 A . 2002 A 9 2003 A 9 2000 B 2 2001 B 3 2002 B 3 2003 B 1 I actually know how to backfill values, but it will backfill all missing values. I want to know how to apply the criterion that only one year's data will be backfilled. Can any one help me with this? Thanks.

SeanZ · ‎04-06-2016

day 1 and 2 are just examples. In reality, the variable day is in terms of date and it's not necessarily continuous. I only want to use the first few observations, say, the first 10 observations in each group (ID). How can that be done?

SeanZ · ‎04-06-2016

Hi, I want to use proc sql to calculate sum of some observations group by certain variable, but subject to some constraint. Below is the detail. I have some stocks and their returns for each day. I would like to calculate the mean of daily returns for each stock ID, but only the first few days, for example, the first two days. ID Day Return 1 1 0.2 1 2 0.2 1 3 0.3 .... 2 1 0.2 2 2 0.1 2 3 0.2 ..... 3 1 0.2 3 2 0.6 3 3 0.2 The program below will give full average of daily returns for each ID, but not the first two days for each ID. How to revise it? proc sql; create table want as select *, mean(return) as avg_ret from from group by ID; quit; Thanks.

Online Status	Offline
Date Last Visited	‎10-11-2021 04:33 PM

how to choose a subsample based on list of values proc sql

Re: proc distance with missing data

Re: proc distance with missing data

Re: proc distance with missing data

Re: proc distance with missing data

Re: proc distance with missing data

proc distance with missing data

Calculate distance between two vectors

Re: backfill some observations

Re: backfill some observations

Re: proc distance with missing data

Re: proc distance with missing data

Re: Calculate distance between two vectors

Re: Calculate distance between two vectors

Re: how to define optional macro parameters?

Re: use proc sql to calculate some variables

how to choose a subsample based on list of values proc sql

Re: proc distance with missing data

Re: proc distance with missing data

Re: proc distance with missing data

Re: proc distance with missing data

Re: proc distance with missing data

proc distance with missing data

Calculate distance between two vectors

Re: backfill some observations

Re: backfill some observations

Re: backfill some observations

Re: backfill some observations

backfill some observations

Re: how to use proc sql to do some special calculation

how to use proc sql to do some special calculation