BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mvniekerk
Calcite | Level 5

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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)
;

View solution in original post

8 REPLIES 8
ballardw
Super User

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
Super User Tom
Super User

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
Calcite | Level 5

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
Super User
Are you pointing to your original data set or your newly created data set with the age variable?
mvniekerk
Calcite | Level 5

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
Super User
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
Super User Tom
Super User

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
Calcite | Level 5

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

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 2482 views
  • 0 likes
  • 4 in conversation