topic Re: Creating variables with proc sql between specific dates in SAS Programming

Creating variables with proc sql between specific dates

Bruce123 — Thu, 12 Sep 2013 07:36:17 GMT

Hello

I am new to the SAS programming world and was hoping someone could help me.

I want to create a new variable in SAS where I ask sas to take the values of variable "performance", but only between a specific time interval, between 31/12//2009 and 31/12/2010 and put this variable in a new column called performance2010. I do not want sas to sum or subract the values of the variable performance. I simply want it to create a new column where only the values of performance from 2010 are in it.

This is my code:

PROC SQL;

CREATE TABLE Performance AS

SELECT*

FROM Return

WHERE Performance = ToDate Where ToDate Between dmy(31, 12, 2009) and dmy(31, 12, 2010) AS Performance2010;

QUIT;

"ToDate" is my date variable and in my data is written by day-months-years

As you can see the second last line is incorrect, but I do not know how to write the last piece of code

Can somehelp me? I would really appreciate it

Thanks

Re: Creating variables with proc sql between specific dates

LinusH — Thu, 12 Sep 2013 08:29:39 GMT

Where is for filtering the whole result set, so it's probably not what you are looking for.

Take a look at CASE, which you could use in the Select statement.

Re: Creating variables with proc sql between specific dates

DJM_SAS — Thu, 12 Sep 2013 08:52:11 GMT

Being a bit rusty on my date/time functions, and being unable to view your current data set, may I suggest the following. I have taken the liberty of renaming the various keywords to better describe your case since the syntax was wrong in the example you gave. Also, since you were ambiguous as to exactly how you'd like it, I've done it two ways:

/* If you really want to create a subset of the original table, do the following */

PROC SQL;

CREATE TABLE Your_New_Table AS

SELECT *, old_performance_variable AS new_performance_variable

FROM your_original_table

WHERE ToDate Between dmy(31,12,2009) and dmy(31,12,2010);

QUIT;

/* If you really want to have the values be missing when performance is not between your two dates, do the following.

PROC SQL;

CREATE TABLE Your_New_Table AS

SELECT *,

CASE

WHEN ToDate BETWEEN dmy(31,12,2009) AND dmy(31,12,2010) THEN old_performance_variable

ELSE . /* have I forgotten how to do missing values nicely? */

END AS new_performance_variable

FROM your_original_table;

QUIT;

Re: Creating variables with proc sql between specific dates

Bruce123 — Thu, 12 Sep 2013 09:43:41 GMT

Hello

Thanks that helped me a lot, and SAS is now completing the procedure.

But one more thing:

I also want to get two other date intervals. One fore 2011 (todate between dmy(31, 12, 2010) and dmy(31, 12 2011)) and 2012 (todate between dmy(31, 12, 2011) and dmy(31, 12, 2012).

Is it possible to do this with the same dataset and in the code-procedure.

I've tried this but Sas only takes one of the dateinterval e.g. it only takes 2011 instead of 2010 and 2011

Could you help out here aswell. I would really appreciate it

Re: Creating variables with proc sql between specific dates

DJM_SAS — Thu, 12 Sep 2013 12:28:53 GMT

Hi Bruce,

Assuming you're using the second of my two pieces of code, you can generate two extra variables by adding in new case statements like so:

PROC SQL;

CREATE TABLE Your_New_Table AS

SELECT *,

CASE

WHEN ToDate BETWEEN dmy(31,12,2009) AND dmy(31,12,2010) THEN old_performance_variable

ELSE .

END AS new_performance_variable,

CASE

WHEN ToDate BETWEEN dmy(31,12,2010) AND dmy(31,12,2011) THEN old_performance_variable

ELSE .

END AS new_performance_variable2,

CASE

WHEN ToDate BETWEEN dmy(31,12,2011) AND dmy(31,12,2012) THEN old_performance_variable

ELSE .

END AS new_performance_variable3

FROM your_original_table;

QUIT;

Note: There's no comma after new_performance_variable3, but there is after 1 and 2.

Alternatively, if you want just one new variable, which includes information if any of those conditions are met and missing otherwise, you can extend out the original case clause like so:

PROC SQL;

CREATE TABLE Your_New_Table AS

SELECT *,

CASE

WHEN ToDate BETWEEN dmy(31,12,2009) AND dmy(31,12,2010) THEN old_performance_variable

WHEN ToDate BETWEEN dmy(31,12,2010) AND dmy(31,12,2011) THEN old_performance_variable

WHEN ToDate BETWEEN dmy(31,12,2011) AND dmy(31,12,2012) THEN old_performance_variable

ELSE .

END AS new_performance_variable

FROM your_original_table;

QUIT;

I'm off to bed for the night, but if you were using the other code framework i prepared instead, it would require some boolean logic (AND and OR clauses with some bracketing) in the where statement.

Re: Creating variables with proc sql between specific dates

Bruce123 — Thu, 12 Sep 2013 12:50:31 GMT

Hello

Yes, I was using the second piece of your code.

Thanks very much for your help. You are my hero

Good night