sql server vs SAS

Accepted Solution Solved
Reply
Trusted Advisor
Posts: 1,228
Accepted Solution

sql server vs SAS


Hi Everyone,

I am just trying to convert following sql server syntax into SAS. I've used this in sql server but not sure what is the equivalent of this in SAS. I am sure someone can suggest me code for this one in SAS.

Here is description of this code:

I've a field "fiscal_year_month" which is numeric and has lenght either 6 or 7 digits. I need to take right 6 digits from this where it has 7 digits and save the results into fiscal_yearmo field.

case when len(fiscal_year_month) = 7 then RIGHT(fiscal_year_month,6) Else fiscal_year_month End as fiscal_yearmo.

Thanks in advance for your help.

Regards,


Accepted Solutions
Solution
‎01-22-2014 02:33 PM
Super User
Super User
Posts: 7,074

Re: sql server vs SAS

If you want the 6 least significant decimal digits of an integer just take the remainder when dividing by a million.  In SAS that is the MOD() function.

data _null_;

  input x;

  y=mod(x,1000000);

  put x= y=;

cards;

1234567

123456

run;

x=1234567 y=234567

x=123456 y=123456

View solution in original post


All Replies
Solution
‎01-22-2014 02:33 PM
Super User
Super User
Posts: 7,074

Re: sql server vs SAS

If you want the 6 least significant decimal digits of an integer just take the remainder when dividing by a million.  In SAS that is the MOD() function.

data _null_;

  input x;

  y=mod(x,1000000);

  put x= y=;

cards;

1234567

123456

run;

x=1234567 y=234567

x=123456 y=123456

Trusted Advisor
Posts: 1,228

Re: sql server vs SAS

Thanks so much Tom. It works but just need to understand login behind it. When number is 1234567 then remainder will be 234567 and I'll take it but when number is 123456 remainder will be

-876544. How can I develop my logic to tell SAS please?

Regards

Super User
Super User
Posts: 7,074

Re: sql server vs SAS

How did you get a negative number? Do you want to get a negative value?  That is not what the string functions you were using in SQL-SERVER will do. It is certainly not what the MOD() function will do.

Modulo is easiest to see with powers of ten as it just results it last n digits. So if you take mod(x,100) you will get the tens and hundreds place.

mod(1234,100) results in 34.  mod(5,100) results in 5.

Super Contributor
Posts: 307

Re: sql server vs SAS

I'm guessing his input variable is actually text, not a number . . .

Super Contributor
Posts: 307

Re: sql server vs SAS

It depends on whether you are connecting to the SQL Server database (explicit SQL pass-through, or implicit pass-through via libname method), or coding against SAS datasets.

With explicit pass-through, you can pass the syntax directly to the SQL Server database for processing.

Implicit pass-through, or coding against SAS datasets, allows you to use the data step approach (as per Tom's example) or PROC SQL functionality -- in either case, you will likely be using SAS-specific functions.

Super User
Posts: 19,851

Re: sql server vs SAS

Not an efficient solution but a quick one.

data have;

input fiscal_year_month;

cards;

2011041

201205

201304

201404

201301

2014024

2013013

2013114

;

data want;

set have;

fiscal_yearmo=input(substr(put(fiscal_year_month, 8. -l), 1, 6), 8.);

run;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 681 views
  • 3 likes
  • 4 in conversation