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

Hi there

I was hoping anyone could help me to generate within the Enterprise Guide recoded column function, the zodiac sign base on dd-mm-yyyy birthdates.

Should i breakdown the dates in Month(x), Day(x)? How can generate a code within the generate new column options?

Aries               March 21 - April 19

Taurus            April  20 - May 20

Gemini            May 21 - June 20

Cancer           June 21 - July 22

Leo                 July 23 - August 22

Virgo               August 23 - September 22

Libra               September 23 - October 22

Scorpio           October 23 - November 21

Sagittarius      November 22 - December 21

Capricorn       December 22 - January 19

Aquarius         January 20 - February 18

Pisces             February 19 - March 20

Thanks in advanced

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHemedinger
Community Manager

Cesar,

That's why you need to recode the dates for use with the format -- take the Year out of the equation.  Zodiac signs don't change from year-to-year (do they?).

In the Query Builder, compute a new column based on the date value, with the month, day, and a fixed year.  I used Year 2000 because it was a leap year and accounts for Feb 29.  Here's an expression you can use:

mdy(month(t1.date),day(t1.date),2000)

Then apply the sign. format in the column properties.

Following: complete program that shows it in action.

/* SAS format for zodiac signs in a given year */

proc format lib=work;

  value sign

  '21Mar2000'd - '19Apr2000'd = 'Aries'

  '20Apr2000'd - '20May2000'd = 'Taurus'

  '21May2000'd - '20Jun2000'd = 'Gemini'

  '21Jun2000'd - '22Jul2000'd = 'Cancer'

  '23Jul2000'd - '22Aug2000'd = 'Leo'

  '23Aug2000'd - '22Sep2000'd = 'Virgo'

  '23Sep2000'd - '22Oct2000'd = 'Libra'

  '23Oct2000'd - '21Nov2000'd = 'Scorpio'

  '22Nov2000'd - '21Dec2000'd = 'Sagittarious'

  /* split Capricorn to make two valid ranges */

  /* that don't span the calendar boundary */

  '22Dec2000'd - '31Dec2000'd = 'Capricorn'

  '01Jan2000'd - '19Jan2000'd = 'Capricorn'

  '20Jan2000'd - '18Feb2000'd = 'Aquarius'

  '19Feb2000'd - '20Mar2000'd = 'Pisces'

  other = 'Unknown';

run;

proc sql;

select air, date format=date9.,

mdy(month(date),day(date),2000) as sign format=sign.

from sashelp.air;

quit;

Output:

signs.PNG

Chris

(updated with image of results)

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

View solution in original post

14 REPLIES 14
ChrisHemedinger
Community Manager

In this blog post I show an approach that uses SAS formats to map dates to Zodiac signs:

What’s our sign? - The SAS Dummy

It doesn't translate exactly to Query Builder, but you could:

- run the code to define the format

- use Query Builder to recode the date to the range the format expects (Mon, Day, Year=2000)

- apply the format to the new column

Chris

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
CesarOmarHR
Calcite | Level 5

Thanks! I'm starting to understand how to generate the dates, however, i deal with an infinite amount of birthdate years, which should be taken into account in the formula and don't have knowledge of.

ChrisHemedinger
Community Manager

Cesar,

That's why you need to recode the dates for use with the format -- take the Year out of the equation.  Zodiac signs don't change from year-to-year (do they?).

In the Query Builder, compute a new column based on the date value, with the month, day, and a fixed year.  I used Year 2000 because it was a leap year and accounts for Feb 29.  Here's an expression you can use:

mdy(month(t1.date),day(t1.date),2000)

Then apply the sign. format in the column properties.

Following: complete program that shows it in action.

/* SAS format for zodiac signs in a given year */

proc format lib=work;

  value sign

  '21Mar2000'd - '19Apr2000'd = 'Aries'

  '20Apr2000'd - '20May2000'd = 'Taurus'

  '21May2000'd - '20Jun2000'd = 'Gemini'

  '21Jun2000'd - '22Jul2000'd = 'Cancer'

  '23Jul2000'd - '22Aug2000'd = 'Leo'

  '23Aug2000'd - '22Sep2000'd = 'Virgo'

  '23Sep2000'd - '22Oct2000'd = 'Libra'

  '23Oct2000'd - '21Nov2000'd = 'Scorpio'

  '22Nov2000'd - '21Dec2000'd = 'Sagittarious'

  /* split Capricorn to make two valid ranges */

  /* that don't span the calendar boundary */

  '22Dec2000'd - '31Dec2000'd = 'Capricorn'

  '01Jan2000'd - '19Jan2000'd = 'Capricorn'

  '20Jan2000'd - '18Feb2000'd = 'Aquarius'

  '19Feb2000'd - '20Mar2000'd = 'Pisces'

  other = 'Unknown';

run;

proc sql;

select air, date format=date9.,

mdy(month(date),day(date),2000) as sign format=sign.

from sashelp.air;

quit;

Output:

signs.PNG

Chris

(updated with image of results)

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
CesarOmarHR
Calcite | Level 5

Thanks Chris@SAS, i'll create the format as you suggest.

CesarOmarHR
Calcite | Level 5

Hi Chris@SAS I'm creating the format to apply the zodiac signs but i'm having trouble getting SAS EG to approve it.

Here i show the specifications i'm using, i though i was writing the dates correctly. I've tried two ways: 01Jan2000'd - 20Jan2000'd and '01Jan2000'd - '20Jan2000'd, but SAS EG is no approving neither of them.

Create Format.GIF

Create Format B.GIF

César

Reeza
Super User

Look at Chris' quotation marks compared to yours.

CesarOmarHR
Calcite | Level 5

I also did the double quotation marks, still.. no approval of my format.

CesarOmarHR
Calcite | Level 5

I don't know what is it that i'm doing wrongCreate Format C.GIF

Tom
Super User Tom
Super User

You could also defined a character format (Call it $MD2SIGN.)  to convert from MMDD values to Zodiak sign.

Then you could calculate the sign using this simple statement.

sign = put(put(dob,mmddyy4.),$md2sign.);

Here is SAS code to generate the format.

proc format lib=work;

  value $md2sign

    '0321' - '0419' = 'Aries'

    '0420' - '0520' = 'Taurus'

    '0521' - '0620' = 'Gemini'

    '0621' - '0722' = 'Cancer'

    '0723' - '0822' = 'Leo'

    '0823' - '0922' = 'Virgo'

    '0923' - '1022' = 'Libra'

    '1023' - '1121' = 'Scorpio'

    '1122' - '1221' = 'Sagittarious'

    '1222' - '1231'

   ,'0101' - '0119' = 'Capricorn'

    '0120' - '0218' = 'Aquarius'

    '0219' - '0320' = 'Pisces'

     other = 'Unknown'

  ;

run;

art297
Opal | Level 21

I know very little about EG but, I seem to recall, having a similar problem with applying date ranges.  While it is totally against everything I've learned in SAS, try it WITHOUT THE QUOTES AND WITHOUT THE ENDING d.

That SHOULDN'T work, but I seem to recall that is how I got it to work.

ZachLain
Calcite | Level 5

Another way to go about this would be to populate a calculated column using a case statement. It would work in much the same way as the method Chris described above but does not require the creation of a format. The following is a case statement that *should*  produce identical results:

CASE WHEN t1.date BETWEEN '21Mar2000' AND '19Apr2000' THEN 'Aries'

     WHEN t1.date BETWEEN '20Apr2000' AND '20May2000' THEN 'Taurus'

     WHEN t1.date BETWEEN '21May2000' AND '20Jun2000' THEN 'Gemini'

     WHEN t1.date BETWEEN '21Jun2000' AND '22Jul2000' THEN 'Cancer'

     WHEN t1.date BETWEEN '23Jul2000' AND '22Aug2000' THEN = 'Leo'

     WHEN t1.date BETWEEN '23Aug2000' AND '22Sep2000' THEN 'Virgo'

     WHEN t1.date BETWEEN '23Sep2000' AND '22Oct2000' THEN 'Libra'

     WHEN t1.date BETWEEN '23Oct2000' AND '21Nov2000' THEN 'Scorpio'

     WHEN t1.date BETWEEN '22Nov2000' AND '21Dec2000' THEN 'Sagittarious'

     WHEN t1.date BETWEEN '22Dec2000' AND '31Dec2000' THEN 'Capricorn'

     WHEN t1.date BETWEEN '01Jan2000' AND '19Jan2000' THEN 'Capricorn'

     WHEN t1.date BETWEEN '20Jan2000' AND '18Feb2000' THEN 'Aquarius'

     WHEN t1.date BETWEEN '19Feb2000' AND '20Mar2000' THEN 'Pisces'

     ELSE 'Unknown'

END

I did not include the 'd behind my date values because in my input table the date column is defined as a string instead of a date. You may have to change it depending on the format of your input. Also, this raises a question that I do not know the answer to (I am a fairly new SAS EG user myself). What are the performance differences between running this case statement vs building a format like Chris described? If I were to run this function on say millions of rows would one run faster than the other? Just curious =]

Hope this helps!

ChrisHemedinger
Community Manager

SAS Formats are pretty fast, and I'm not sure you would see a big difference in a SAS data table.  But if your data source is in a database, PROC SQL can push the logic into the database process and perform the operation there, and the result could come back a lot quicker.

For example, suppose you want to run a query on a database (Oracle, let's say) of 1 million records - names and birthdays.  You want to know the freq distribution of the Zodiac signs across this population -- counts, essentially.

With a SAS format, PROC SQL has to pull all records from Oracle to your SAS session, apply the format, and then count the record into one of the 12 "sign" groups.  But if your SQL code can run in the Oracle process, then the database evaluates the CASE and adds the result to the count (assuming you are doing a GROUP BY Sign).  At the end, only 12 records come over into SAS -- one for each Zodiac sign and its aggregated count.

You've saved lots of I/O processing, moving 12 records instead of 1 million.  If there is a difference in the speed of SAS formats and a CASE statement, it's most likely negligible compared to the I/O cost .

Note: there *is* a way for you to "install" SAS formats into a database by using SAS In-Database Technologies.  That helps you to get the best of both worlds: flexibility and power of Formats, with the "local" processing in the database.

Chris

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
ZachLain
Calcite | Level 5

That was very informative. Thanks for the quick reply!

CesarOmarHR
Calcite | Level 5

Thanks Chris@SAS it all worked correctly.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 14 replies
  • 6035 views
  • 8 likes
  • 6 in conversation