Problem with dividing a variable by a constant to create a new variable

[mvniekerk]

Good day.

I have a variable that I calculated, which is "age" in days, by substracting two dates, giving me the value in days (variable called age, no problem).  I now want to take that variable and create a new variable which will be age in months (so divide by 30.5).  Here is my proc sql code:

proc sql;
create table work.data_want as
select *,

/* age_months */
(t1.age / 30.5) as age_months
from work.data_have t1; 

quit;

This is very straightforward, but for some reason it does not want to work?  I also tried a IFN function, to no avail.  Nowhere do I get a error message.  The new variable (column) is created, but only has missing values in it.

Can someone please help?

Thank you in advance.

[ballardw]

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the <> to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the <> icon or attached as text to show exactly what you have and that we can test code against.

Since you say that you started with two dates you might investigate the INTCK function for returning intervals between dates as well, which I would do at the same time that you calculate the age in days.

[Tom]

You said you calculated AGE_MONTHS this way:

(t1.age / 30.5) as age_months

If AGE_MONTHS is missing for every observation then AGE must have been missing.

[mvniekerk]

Tom
It does calculate it, age in days and age in months. But for some reason if I move the column age_months, and write it to a .txt file (as I always do), it delete all the values so that there is only missing values. This is very strange to me?

[Reeza]

Are you pointing to your original data set or your newly created data set with the age variable?

[mvniekerk]

To the newly created dataset.  If I move the position of the column with the newly created age_months variable to another position, from the last column where it was created, to a column position earlier in the dataset, it delete all the values so that they are missing.  In other words, in every proc sql I do, I create a new variable (column).  Then at the end I want my dataset with the columns in a certain order.  When I shift that age_months column is when the aforementioned problem occurs?

[Reeza]

Show your full code and log. What you think is happening isn't happening somewhere but we can't see what without seeing the actual code and log.

[Tom · Accepted solution]

You cannot have two variables with the same name in a dataset.  If you select two or more variables with the same name then PROC SQL will keep only the first one.

So if you do something like:

create table step2 as
select *, age/30.5 as age_months
from step1
;

And STEP1 already had a variable named AGE_MONTHS then the calculated value is discarded because there is not place to save it.

Try like this instead.

create table step2 as
select *, age/30.5 as age_months
from step1(drop=age_months)
;

[mvniekerk]

Thank you very much Tom.  You are correct!  Problem solved.  I see SAS is very sensitive in that way.