About aaou

aaou · ‎08-18-2016

Sorry to bother you. Could you retype the final part: data want; set temp; by id; &arrays if first.id then n=0; n+1; ∑ &v if n gt 3; keep id year sum_:; run;

aaou · ‎08-18-2016

Thank you! Sorry to trouble you, but could you tell give me the correct code for the final part: data want; set temp; by id; &arrays if first.id then n=0; n+1; ∑ &v if n gt 3; keep id year sum_:; run; I replaced ∑ but the resulting output has empty values now for the summed columns.

aaou · ‎08-17-2016

Hey thank you so much! Instead of var1 and var2, if I have a lot of variables that I want to do the same thing as above how would you code it? So that I don't have to keep on typing the variable names...

aaou · ‎08-17-2016

Hi, So for example the ID = 200 is missing in 2012. So the output for ID=200 in 2015 should be the sum of only 2013 and 2014. So even if an ID is missing in the previous three years the sum should take only whatever the values that are available from the previous three years. If there is only 1 year for an ID from the previous year, the sum would be only the value of that avaialble year. There was a slight error in the example. I adjusted it. Hope you can help me out now to get the given output file. Thank you!!

aaou · ‎08-17-2016

Hi, Assume I have data set like the one below: ID Year Var1 Var2 123 2012 345 24 310 2012 4 324 400 2012 234 456 123 2013 675 46 200 2013 577 89 310 2013 46 45 400 2013 45 76 123 2014 123 234 200 2014 1234 35 400 2014 435 645 123 2015 231 236 200 2015 456 87 310 2015 435 534 400 2015 232 235 123 2016 123 679 200 2016 354 523 310 2016 233 754 400 2016 200 342 Now suppose, I want to create a new data set from this data set where for each year and ID I want to get the summation of the previous three years values for var 1 and var2...so that I get an output like this: ID Year Var1 Var2 123 2015 1143 304 200 2015 1811 124 310 2015 50 369 400 2015 714 1177 123 2016 1029 516 200 2016 2267 211 310 2016 481 579 400 2016 712 956 So for example, for ID 400 in 2016 I get the sum of the values for VAR 1 and VAR 2 in years 2015, 2014, 2013. Notice that not in all years do all the IDs appear. Also suppose I have a large number of variables (VAR1 TO VARn) so I don't want to be typing in all their names. How do I go about this? Thank you!

aaou · ‎08-02-2016

Hi thanks for your response. Suppose instead of Math, Science, Music and Latin I had a large number of other variables...then how would you alter the code...to have it in a way that I don't have to type each and every variable?

aaou · ‎06-29-2016

Thanks! Sorry to keep on troubling you, Then I get the attached error. The variables in ds2 that I want to add up are numerical. And the year and ID are character. I double checked. So I'm not sure why I'm getting this error. I'm assuming it's reffering to this portion of the code ds1.Year-ds2.Year2 between 1 and 3;

aaou · ‎06-29-2016

Thanks! I changed it as follows: proc sql ; create table ipo_lag3(drop=CUSIP2 Year2) as select * from ma.sample_firms as ds1 left join ma.ipo_tranposed_new as ds2(rename=(Year=Year2 CUSIP=CUSIP2))on ds1.CUSIP=ds2.CUSIP2 and ds1.Year-ds2.Year2 between 1 and 3; quit; But I get the attached error.

aaou · ‎06-29-2016

Thank you very much. Your code works perfectly for the example I have given. However, when I run it with my actual datasets it gives an error. I have copy pasted the code and the error. Could you show me what I'm doing wrong? proc sql ; create table ipo_lag3(drop=CUSIP2 Year2) as select * from ma.sample_firms left join ma.ipo_tranposed_new (rename=(Year=Year2 CUSIP=CUSIP2)) on ma.sample_firms.CUSIP=ma.ipo_tranposed_new.CUSIP2 and ma.sample_firms.Year-ma.ipo_tranposed_new.Year2 between 1 and 3; quit; *ds1 is ma.sample_firms, ds2 is ma.ipo_transposed_new, ds3 is ipo_lag3 and ID is CUSIP; I have attached a screenshot of the error I get. Thanks!

aaou · ‎06-29-2016

I'm new to programming,so only if you have the time, would you mind dissecting your code a bit; so that I understand each statement better? Only if you don't mind. Also in the original dataset I'm dealing with, I have a large number of variables that I need to add up. So instead of listing each variable when I'm creating the table using sql,is there an easier way of referring to all the variables I need to add in this manner? Thank you!

aaou · ‎06-29-2016

Hi John, Thank you very much! much apreciated. 🙂

aaou · ‎06-29-2016

I have a dataset where each oberservation is identified with a unique ID and year (Data Set1). I have another data set also with ID and years and values for several other variables. For each observation in Data Set1, I want to look at Data Set 2 and if that same ID exists then I want to sum up the values for the previous 3 years in Data Set 2. For example I'm looking at something like this: Dataset1 Year ID 2014 456 2014 678 2014 709 2015 346 2015 456 2015 567 2016 678 2016 709 2016 891 Dataset2 ID Year Math Science Music Latin 300 2011 56 32 54 45 456 2011 85 87 89 95 678 2011 76 87 56 76 456 2012 88 89 90 93 567 2012 50 54 56 57 456 2013 86 87 93 94 300 2013 13 34 54 35 567 2013 45 48 56 59 456 2014 85 87 88 95 200 2014 56 58 54 56 678 2014 70 76 75 78 567 2014 58 58 50 55 678 2015 74 76 78 70 300 2015 34 32 14 26 The output I'm looking at: ID Year Math Science Music Latin 456 2014 259 263 272 282 678 2014 76 87 56 76 709 2014 0 0 0 0 346 2015 0 0 0 0 456 2015 259 263 271 282 567 2015 153 160 162 171 678 2016 144 152 153 148 709 2016 0 0 0 0 891 2016 0 0 0 0 Thank you!

aaou · ‎06-27-2016

It's like this, these variables have values for certain years. But are empty for some years. So when I want to extract a dataset for a particular year from the main dataset, I want in that dataset all the null columns not to appear. Could you advice me how to do this?

aaou · ‎06-25-2016

Is there a way to do this without specifying the names of the variables?

aaou · ‎06-23-2016

What's the easiest way to remove empty columns/variables from a data set? As in when I get a subset of data from my main dataset, if there are columns which are empty/null in that subset, how do I write a program to remove such variables from the subset? Thanks!

Online Status	Offline
Date Last Visited	‎09-01-2016 10:08 AM

Re: Prevent COMPGED score changing

Re: Fuzzy match with soundex and compged

Prevent COMPGED score changing

Re: Fuzzy match with soundex and compged

Re: Fuzzy match with soundex and compged

Fuzzy match with soundex and compged

Fuzzy match on the same variables

Re: Selecting all variables

Selecting all variables

Re: Sum up lagged values

Re: Selecting all variables

Re: Sum up lagged values

Re: Sum up lagged values

Re: Sum up lagged values

Re: Summing up values for lagged years

Calculating percentages across row

Re: Sum up lagged values

Re: Sum up lagged values

Re: Sum up lagged values

Re: Sum up lagged values

Sum up lagged values

Re: Summing up values for lagged years

Re: Summing up values for lagged years

Re: Summing up values for lagged years

Re: Summing up values for lagged years

Re: Summing up values for lagged years

Re: Summing up values for lagged years

Summing up values for lagged years

Re: Delete empty columns

Re: Delete empty columns

Delete empty columns